get the id based on condition in group by

Question

I'm trying to create a sql query to merge rows where there are equal dates. the idea is to do this based on the highest amount of hours, so that i in the end gets the corresponding id for each date with the highest amount of hours. i've been trying to do with a simple group by, but does not seem to work, since i CANT just put a aggregate function on id column, since it should be based the hours condition

+------+-------+--------------------------------------+
| id   | date           | hours                       |
+------+-------+--------------------------------------+
| 1    | 2012-01-01     | 37                          |
| 2    | 2012-01-01     | 10                          |
| 3    | 2012-01-01     | 5                           |
| 4    | 2012-01-02     | 37                          |
+------+-------+--------------------------------------+

desired result

+------+-------+--------------------------------------+
| id   | date           | hours                       |
+------+-------+--------------------------------------+
| 1    | 2012-01-01     | 37                          |
| 4    | 2012-01-02     | 37                          |
+------+-------+--------------------------------------+

I removed the incompatible database tags. Please tag only with the database you are really using. — Gordon Linoff, Jun 12 '18 at 21:22
I added the greatest-n-per-group tag. This question has been asked in different forms hundreds of times, and there are many solutions. Some depend on the specific brand of RDBMS you use. — Bill Karwin, Jun 12 '18 at 21:57

Gordon Linoff · Accepted Answer · 2018-06-12T21:24:23.730

If you want exactly one row -- even if there are ties -- then use row_number():

select t.*
from (select t.*, row_number() over (partition by date order by hours desc) as seqnum
      from t
     ) t
where seqnum = 1;

Ironically, both Postgres and Oracle (the original tags) have what I would consider to be better ways of doing this, but they are quite different.

Postgres:

select distinct on (date) t.*
from t
order by date, hours desc;

Oracle:

select date, max(hours) as hours,
       max(id) keep (dense_rank first over order by hours desc) as id
from t
group by date;

score 0 · Answer 2 · answered Jun 12 '18 at 21:22

0

Here's one approach using row_number:

select id, dt, hours
from (
    select id, dt, hours, row_number() over (partition by dt order by hours desc) rn
    from yourtable
) t
where rn = 1

answered Jun 12 '18 at 21:22

sgeddes

62,311
6
61
83

score 0 · Answer 3 · edited Jul 24 '18 at 19:40

0

You can use subquery with correlation approach :

select t.*
from table t
where id = (select t1.id
            from table t1
            where t1.date = t.date
            order by t1.hours desc
            limit 1);

In Oracle you can use fetch first 1 row only in subquery instead of LIMIT clause.

edited Jul 24 '18 at 19:40

marc_s

732,580
175
1,330
1,459

answered Jun 12 '18 at 21:23

Yogesh Sharma

49,870
5
26
52

get the id based on condition in group by

3 Answers3