CODEWITHSUNDEEP
mysqldateselect
Franco
Franco

Reputation: 2926

MySQL query for selecting distinct field and then ordering by date

I would like to sort a table by date. I have multiple records for the same artist name but they have different dates

e.g.

ARTIST:DATE
Gerd:2011-09-28
Gerd:2011-09-01
Simon:2011-07-01
Simon:2011-10-02
Franco:2011-01-10
Franco:2011-09-15
Franco:2011-07-01
Des:2011-09-05

How can I extract the distinct username and show the most recent date that they had a record created on? I would also like to only show names that have more than 2 records

so in this case the results I want are

Simon:2011-10-02
Gerd:2011-09-28
Franco:2011-09-15

(I would like these to be sorted in date order)

Thanks!

Upvotes: 0

Views: 131

Answers (1)

p.campbell
p.campbell

Reputation: 100567

Try this:

SELECT Artist, MAX(Date) AS MaxDate
FROM Artists
GROUP BY Artist
HAVING COUNT(Artist) > 2 
ORDER BY MaxDate DESC

Your question explicitly states "more than 2", but your example data illustrates >= 2. In any case, you can adjust the HAVING if/as you require:

HAVING COUNT(Artist) >=2

Upvotes: 3

Related Questions