Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
CREATE TABLE employee( name, sdate, subid) AS
SELECT 'Aarna',  DATE '2016-03-01',  123 FROM DUAL UNION ALL
SELECT 'Aarna',  DATE '2016-03-01',  453 FROM DUAL UNION ALL
SELECT 'Brinda', DATE '2016-03-01',   12 FROM DUAL UNION ALL
SELECT 'Brinda', DATE '2016-03-01',   45 FROM DUAL UNION ALL
SELECT 'Brinda', DATE '2016-03-01',   16 FROM DUAL UNION ALL
SELECT 'Brinda', DATE '2016-03-01',   18 FROM DUAL UNION ALL
SELECT 'Chara',  DATE '2016-03-01', 1600 FROM DUAL UNION ALL
SELECT 'Chara',  DATE '2016-03-01', 1820 FROM DUAL
8 rows affected

SELECT name,
       TRUNC(sdate) AS day,
       MIN(subid) AS subid
FROM   employee
GROUP BY name, TRUNC(sdate)
ORDER BY COUNT(*) DESC, name
FETCH FIRST ROW ONLY


NAME
DAY
SUBID


Brinda
2016-03-01 00:00:00
12


SELECT name,
       TRUNC(sdate) AS day,
       MIN(subid) AS subid,
       ROW_NUMBER() OVER (
         PARTITION BY TRUNC(sdate)
         ORDER BY COUNT(*) DESC, name
       ) AS rn
FROM   employee
GROUP BY name, TRUNC(sdate)
ORDER BY rn
FETCH FIRST ROW WITH TIES


NAME
DAY
SUBID
RN


Brinda
2016-03-01 00:00:00
12
1


INSERT INTO employee( name, sdate, subid)
SELECT 'Aarna',  DATE '2016-03-01',    1 FROM DUAL UNION ALL
SELECT 'Aarna',  DATE '2016-03-01',   42 FROM DUAL;
2 rows affected

SELECT name,
       TRUNC(sdate) AS day,
       MIN(subid) AS subid
FROM   employee
GROUP BY name, TRUNC(sdate)
ORDER BY COUNT(*) DESC, name
FETCH FIRST ROW ONLY


NAME
DAY
SUBID


Aarna
2016-03-01 00:00:00
1


SELECT name,
       TRUNC(sdate) AS day,
       MIN(subid) AS subid,
       ROW_NUMBER() OVER (
         PARTITION BY TRUNC(sdate)
         ORDER BY COUNT(*) DESC, name
       ) AS rn
FROM   employee
GROUP BY name, TRUNC(sdate)
ORDER BY rn
FETCH FIRST ROW WITH TIES


NAME
DAY
SUBID
RN


Aarna
2016-03-01 00:00:00
1
1


SELECT name,
       day,
       subid
FROM   (
  SELECT name,
       TRUNC(sdate) AS day,
       MIN(subid) AS subid,
       ROW_NUMBER() OVER (
         PARTITION BY TRUNC(sdate)
         ORDER BY COUNT(*) DESC, name
       ) AS rn
FROM   employee
GROUP BY name, TRUNC(sdate)
)
WHERE  rn = 1;


NAME
DAY
SUBID


Aarna
2016-03-01 00:00:00
1