CODEWITHSUNDEEP
mysqlsql
tread
tread

Reputation: 11098

How to Select record with the maximum value in a GROUP BY?

Hi I have a table with duplicate id's that link different tasks with the completion level of each displayed as follows:

id      date                    perc     taskid
4722    2007-11-08 16:20:14     0.00      3
4724    2007-11-20 15:32:40     25.71     5
4722    2007-11-08 16:20:14     0.00      8
4727    2008-01-29 10:19:39     0.00      18
4726    2008-01-28 11:44:50     7.14      13
4726    2008-01-28 11:44:50     34.29     90
4728    2008-02-11 13:14:14     2.86      21

I would like to return distinct id's with the highest percentage...ie.

4722    2007-11-08 16:20:14     0.00      3
4724    2007-11-20 15:32:40     25.71     5
4727    2008-01-29 10:19:39     0.00      18
4726    2008-01-28 11:44:50     34.29     90
4728    2008-02-11 13:14:14     2.86      21

I will be using a GROUP BY I assume, but how will I get the max of each column?

Bonus...Is it possible to sort further by the most recent date if percentages are the same?

UPDATE: I would like the taskid that has the highest percentage to remain with the record?

Upvotes: 1

Views: 68

Answers (2)

Adriaan Stander
Adriaan Stander

Reputation: 166396

Are you looking for

SELECT id, date, MAX(perc) as MAX_PERC
FROM YourTable
GROUP BY id, date

Is it possible that the dates for a given id might differ between entries? Or will they always be the same date?

If so, you might want to take a look at The Rows Holding the Group-wise Maximum of a Certain Column

Upvotes: 5

GautamD31
GautamD31

Reputation: 28763

Try like

SELECT id,date,MAX(perc) AS MAX_PERCENT
FROM My_Table
GROUP BY id

You can also GROUP BY date.

Upvotes: 0

Related Questions