CODEWITHSUNDEEP
mysqlsql
JohnSmith
JohnSmith

Reputation: 1557

MYSQL - How to get the highest value of each record?

For example I have these table...

ID | SCORE | DATE
 1 | 10    | 2-2-2012
 2 | 20    | 2-2-2012
 3 | 20    | 2-2-2012
 2 | 5     | 4-2-2012
 4 | 30    | 4-5-2012
 1 | 20    | 5-5-2012

I want to get the score of each ID with the latest date.

My expect output is...

ID | SCORE | DATE
 1 | 20    | 5-5-2012
 2 | 5     | 4-2-2012
 3 | 20    | 2-2-2012
 4 | 30    | 4-5-2012

Is it possible?

Thanks in advance!

Upvotes: 0

Views: 115

Answers (3)

bitoshi.n
bitoshi.n

Reputation: 2318

Try

SELECT id, score, date
FROM   table1 t1
WHERE  date=(SELECT MAX(t2.date)
              FROM table1 t2
              WHERE t1.id = t2.id);

Upvotes: 2

user648129
user648129

Reputation:

In case you don't care about getting top score for case if two rows with same id have same date:

select *,max(DATE) as max_date FROM table group by ID order by max_date desc;

Upvotes: 0

zerkms
zerkms

Reputation: 255095

    SELECT t1.*
      FROM tbl t1
INNER JOIN (SELECT MAX(date) max_date,
                   id
              FROM tbl
          GROUP BY id) t2 ON t1.id = t2.id
                         AND t1.date = t2.max_date

Upvotes: 2

Related Questions