Limit? Group? Distinct?

askthefool

Trying to make a top 10 thing.

The table structure is

CREATE TABLE blog_music (
id int(8) NOT NULL auto_increment,
song varchar(100) default NULL,
date timestamp(14) NOT NULL,
PRIMARY KEY (id),
UNIQUE KEY id (id)
) TYPE=MyISAM;

I want to extract, the top 10 song names, the last time it was played (which would be from the last row), and a count of how many times it was played.

I'm able to get the song name, and the number of times using this query, but I cant get the time to work with it.

$result = mysql_query("SELECT song,count(*) AS num FROM blog_music group by song ORDER BY num desc LIMIT 10");

And I keep forgetting how to extract time from a timestamp, but I'm sure I can figure that out. If somoene can show me an proper query for what I'm trying to do I'd really appreciate it. Thanks in advance for the help

Edit ***
I think I can do it using nested while loops, but is it possible to do it with a single query or less than 10? If yes, what would the query be?

barand

I think you will have to write the selection you have at at present to a temp table

$sql = "SELECT song,count(*) AS num INTO temp FROM blog_music group by song ".
"ORDER BY num desc LIMIT 10";

then join the temp table with your original in a second query and extract max(timestamp) to get the last time it was played.

hth

barand

On further reflection, just adding "max(timestamp) as latestdate" to your original selection might do the trick in one go.

askthefool

Thanks barnard.

I figured that out my self later last night. What my final query turned out to be was

$result = mysql_query("SELECT song,MAX(date) as date, count(*) AS num FROM blog_music group by song ORDER BY num desc LIMIT 10");

askthefool

One more thing. This one is simpler but I can't get it.

How about the last 10 unique songs, and the number of times they were played?