[RESOLVED] Result order from query

[ClarkF1]

Hi,

I have a chart of top 20 artists that i've listened to.

All the artist information (over 200 artists) is stored in a database with the last time a song was played (time in UNIX timestamp form), artist (VARCHAR) and trackname (VARCHAR)

I use the query:

"SELECT artist,time, COUNT(*) AS totals FROM songs GROUP BY artist ORDER BY totals DESC, time DESC LIMIT 20"

This counts the number of times the artist appears in the database. I want to display the data so that if two or more artists have the same playcount (totals), they are ordered by the last time one of their tracks was played.

The problem I have is that the time retrieved for each artist is the earliest time the track was played not the latest.

e.g.

I have played two Queen tracks, one last Saturday and one on Tuesday.
I have played two Metallica tracks, last on Sunday and one on Monday.

In this case, in the chart, Metallica would appear above Queen despite Queen being played more recently because the first Metallica track was played after the first Queen track.

How can I get it to order by latest track?

It's got something to do with the GROUP aspect i believe

[tomhath]

This might be what you want, untested:

SELECT artist, MAX(time) as mostrecent, COUNT(*) AS totals
FROM songs
GROUP BY artist
ORDER BY totals DESC, mostrecent DESC LIMIT 20

[ClarkF1]

That worked a treat thanks.........

😃