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:
GROUP BY
the rows byname
and dayTRUNC(dt)
;- Find the
MIN
imumsubid
in each group; ORDER
the groups inDESC
ending order of the number of rows in each group (COUNT(*)
) and then, if there are ties, inASC
ending order ofname
.FETCH
only theFIRST 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:
NAME | DAY | SUBID |
---|---|---|
Brinda | 2016-03-01 00:00:00 | 12 |
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:
NAME | DAY | SUBID |
---|---|---|
Aarna | 2016-03-01 00:00:00 | 1 |
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.