Group By Two Columns and Taking Maximum Value of Another Column-CodePudding

I have a table which is called "dataset" and has 3 columns (driverid, week, loghour)

driverId   week     loghour
int         int        int

Example Data

10,1,70
10,2,70
10,5,70
10,10,50
10,47,74
11,11,77
11,15,0
11,23,70
11,32,82
13,35,51
14,8,60
14,11,45

Question

Show for each driver their ID and the week in which they drove the greatest number of hours

My solution

select driverid, week, max(loghour) as maxhours from dataset group by driverid, week order by maxhours desc;

My solution doesn't work.

Expected Output:

10,47,74
11,32,82
13,35,51
14,8,60

Note: For those who want to try, I am giving the table creation and insert codes to avoid time waste.

CREATE TABLE dataset (driverid int, week int, loghour int) ;

INSERT INTO dataset (driverid, week, loghour) VALUES (10,1,70), (10,2,70), (10,5,70), (10,10,50), (10,47,74), (11,11,77), (11,15,0), (11,23,70), (11,32,82), (13,35,51), (14,8,60), (14,11,45) ;

CodePudding user response：

Two table join sql.

 select  dataset.* from 
(select  driverid, max(loghour) as maxhours from dataset
 group by driverid) t  
 join   dataset  on dataset.driverid = t.driverid and t.maxhours = dataset.loghour
 order by dataset.driverid

output is

10 47 74

11 32 82

13 35 51

14 8 60

CodePudding user response：

Take a look at the documentation for the rank() function: https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html#function_rank

Create a SELECT whose output rows mention both week and rank.

Now it's easy -- pick out the rows with rank of 1 to find the answer.

It is possible for two or more weeks to be best, having identical hours. Depending on your requirements, outputting multiple tied week IDs might be correct. Or you might want to use a GROUP BY to arbitrarily output the MIN() or MAX() week ID.

CodePudding user response：

Here is a query using correlated subquery in WHERE clause:

select * 
from dataset d
where loghour=(select max(loghour) from dataset where driverid=d.driverid)
;