How to get max participated each day id's | Oracle

I am little bit stuck how to write query for certain scenario

Table : employee

create table employee
(
   name varchar2(10),
   sdate date,
   subid number
);

insert into dummy values ('Arun',to_date('2016-03-01','YYYY-MM-DD'),123);
insert into dummy values ('Arun',to_date('2016-03-01','YYYY-MM-DD'),453);
insert into dummy values ('Raj',to_date('2016-03-01','YYYY-MM-DD'),12);
insert into dummy values ('Raj',to_date('2016-03-01','YYYY-MM-DD'),45);
insert into dummy values ('Raj',to_date('2016-03-01','YYYY-MM-DD'),16);
insert into dummy values ('Raj',to_date('2016-03-01','YYYY-MM-DD'),18);
insert into dummy values ('Darshan',to_date('2016-03-01','YYYY-MM-DD'),1600);
insert into dummy values ('Darshan',to_date('2016-03-01','YYYY-MM-DD'),1820);

Below is the data I have in table

name,date,subid
Arun,2016-03-01,123
Arun,2016-03-01,453
Raj,2016-03-01,12
Raj,2016-03-01,45
Raj,2016-03-01,16
Raj,2016-03-01,18
Darshan,2016-03-01,1600
Darshan,2016-03-01,1820

Now I need to filter in such way that maximum time subid participated for name each day

Arun    -> count -> 2  -> 2016-03-01
Raj     -> count -> 4  -> 2016-03-01
Darshan -> count -> 2  -> 2016-03-01

Output :

name , subid , date
Raj  , 12    , 2016-03-01   

For suppose if this occurs like Raj and Darshan as same count then order name wise and pick lesser subid for that name

name,date,subid
Arun,2016-03-01,123
Arun,2016-03-01,453
Raj,2016-03-01,12
Raj,2016-03-01,45
Raj,2016-03-01,16
Raj,2016-03-01,18
Darshan,2016-03-01,1600
Darshan,2016-03-01,1820
Darshan,2016-03-01,160
Darshan,2016-03-01,18

Arun    -> count -> 2  -> 2016-03-01
Raj     -> count -> 4  -> 2016-03-01
Darshan -> count -> 4  -> 2016-03-01

Alpabetical order

Arun    -> count -> 2  -> 2016-03-01
Darshan -> count -> 4  -> 2016-03-01
Raj     -> count -> 4  -> 2016-03-01

Pick lesser subid for Darshan

Output :

name      , subid , date
Darshan   , 18    , 2016-03-01

I cannot able to identify how to achieve it.

I thought of doing partition by on what basis need to partition ?

>Solution :

You appear to want to:

1. GROUP BY the rows by name and day TRUNC(dt);
2. Find the MINimum subid in each group;
3. ORDER the groups in DESCending order of the number of rows in each group (COUNT(*)) and then, if there are ties, in ASCending order of name.
4. FETCH only the FIRST ROW, which will have the greatest count and earliest name.

Like this:

SELECT name,
       TRUNC(dt) AS dt,
       MIN(subid) AS subid
FROM   table_name
GROUP BY name, TRUNC(dt)
ORDER BY COUNT(*) DESC, name ASC
FETCH FIRST ROW ONLY

Which, for the sample data:

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

Outputs:

If you then insert a couple more rows:

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;

and run the same query again, the output is now:

If you have multiple days then you can use ROW_NUMBER to find the maximum count and then first name for each day:

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;

or:

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

However, your data does not have multiple days so the output would be identical.

fiddle