Counting duplicate entries and ranking them

[clickblipclick]

MySQL client version: 4.1.22

 

I kind of think there's no "easy" way to do what I want... but we'll see.

 

I'm working on a college radio station website and I have a table set up that contains all the tracks the DJs play.  The table is set up like this:

 

CREATE TABLE `playlist` (

  `id` int(10) NOT NULL auto_increment,

`title` varchar(255) NOT NULL default '',

  `artist` varchar(255) NOT NULL default '',

  `comments` text NOT NULL,

  `show_id` int(10) NOT NULL default '0',

  `list_id` int(10) NOT NULL default '0',

  `time_played` timestamp NOT NULL default CURRENT_TIMESTAMP,

  PRIMARY KEY  (`id`)

) ENGINE=MyISAM AUTO_INCREMENT=76 DEFAULT CHARSET=latin1

 

What I would like to do is be able to display the top artists and top tracks.  For top artists, I would like a ranked list of artists that appear the most often in the table.  So, if there are 50 "artist" entries that say "Kanye West", and 49 that say "Pulp", Kanye west would come first and Pulp would come second.

 

The top songs would be the same except artist and title would need to both be duplicate.

 

I'm not really sure how to approach this (I'm sort of new to this stuff).  I'm not wanting you to write the code for me or anything... Just nudge me in the direction of how you might go about this.  (Another table?)

 

I'm using PHP on the site, FYI.

 

Thanks.

 

edit:  If you're familiar with http://www.last.fm, those are the kind of charts I'd like to create.

[fenway]

You can use GROUP BY on artist/title with a COUNT().

[clickblipclick]

Hmm.  Great tip.  By doing the following query I could get the artists to show up along with the amount of times they've been played:

 

SELECT artist, COUNT(title) FROM playlist GROUP BY artist

 

Is there any way you can think of that would have it so it prints in the order of the count?  You can't say "ORDER BY COUNT(title)" unfortunately...  Any other ideas?

 

 

[clickblipclick]

Oh, I think I figured it out!  Thanks!

[aschk]

For those interested I expect the solution click found was the following:

 

SELECT artist, COUNT(title) as 'num' FROM playlist GROUP BY artist ORDER BY num

[clickblipclick]

Yep, that's how I did it.