CODEWITHSUNDEEP
mysqldistinct
Aviram
Aviram

Reputation: 3067

MySQL distinct on one column with other max column

Let's say I have this query:

SELECT report_id, time_sent
FROM  report_submissions
WHERE report_id IN (1,2,3,4,5,6)
ORDER BY report_id ASC, time_sent DESC

with this result:

report_id   time_sent
1           2
1           1
2           4
2           3
3           4

And I want to change that query so I will get a DISTINCT report_id with its max(time_sent), for example:

report_id   time_sent
1           2
2           4
3           4

How do I do that in the most efficient way? Thanks.

Upvotes: 0

Views: 346

Answers (3)

Kevin Burton
Kevin Burton

Reputation: 11936

you need to use group by

see: http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html

Upvotes: 0

Widor
Widor

Reputation: 13275

DISTINCT is a lazy GROUP BY!

SELECT report_id, max(time_sent)
FROM  report_submissions
WHERE report_id IN (1,2,3,4,5,6)
GROUP BY report_id
ORDER BY report_id ASC, max(time_sent) DESC

Upvotes: 0

bpgergo
bpgergo

Reputation: 16037

SELECT report_id, max(time_sent)
FROM  report_submissions
WHERE report_id IN (1,2,3,4,5,6)
GROUP BY report_id
ORDER BY report_id ASC

Upvotes: 2

Related Questions