sql select/grouping question
-
Hello there I have a table which contains the following fields... artist title count_played date_title_played I am trying to figure out how to present a top ten most played, but I can't seem to get my head around how to stop it showing duplicate count_played. At the moment I have used the following Select...
SELECT top 10 artist, title, count_played, date_title_played
FROM dbo.songlist
WHERE count_played >0
ORDER BY count_played DESC, date_title_played DESCHowever this is what shows...
Artist Title Count Last Played
Wanted By The FBI Until the End of the World 22 09/03/2010 11:22:16
Koko Conley Keep Your Head Up 22 09/03/2010 10:35:07
Red Sun Compromise 21 09/03/2010 11:08:57
Cara Friend of Mine 21 09/03/2010 11:01:09
Preet Whos going to love you live 20 09/03/2010 10:51:22
The Ambersons Oh, my Isabelle 19 09/03/2010 10:51:22
Patchwork Grace Boozawaffle 19 09/03/2010 10:49:01
VK Lynne Find Me 18 09/03/2010 10:45:33
Clara Barker Wishing 17 09/03/2010 10:43:45
Red Sun Just To Hold You 17 09/03/2010 10:11:00What I actually want is...
Artist Title Count Last Played
Wanted By The FBI Until the End of the World 22 09/03/2010 11:22:16
Red Sun Compromise 21 09/03/2010 11:08:57
Preet Whos going to love you live 20 09/03/2010 10:51:22
The Ambersons Oh, my Isabelle 19 09/03/2010 10:51:22
VK Lynne Find Me 18 09/03/2010 10:45:33
Clara Barker Wishing 17 09/03/2010 10:43:45and so on. I am not that experienced with sql queries but if someone could point me in the right direction on this it would be very much appreciated. Is it some sort of grouping that i need to use? Regards Ray
-
Hello there I have a table which contains the following fields... artist title count_played date_title_played I am trying to figure out how to present a top ten most played, but I can't seem to get my head around how to stop it showing duplicate count_played. At the moment I have used the following Select...
SELECT top 10 artist, title, count_played, date_title_played
FROM dbo.songlist
WHERE count_played >0
ORDER BY count_played DESC, date_title_played DESCHowever this is what shows...
Artist Title Count Last Played
Wanted By The FBI Until the End of the World 22 09/03/2010 11:22:16
Koko Conley Keep Your Head Up 22 09/03/2010 10:35:07
Red Sun Compromise 21 09/03/2010 11:08:57
Cara Friend of Mine 21 09/03/2010 11:01:09
Preet Whos going to love you live 20 09/03/2010 10:51:22
The Ambersons Oh, my Isabelle 19 09/03/2010 10:51:22
Patchwork Grace Boozawaffle 19 09/03/2010 10:49:01
VK Lynne Find Me 18 09/03/2010 10:45:33
Clara Barker Wishing 17 09/03/2010 10:43:45
Red Sun Just To Hold You 17 09/03/2010 10:11:00What I actually want is...
Artist Title Count Last Played
Wanted By The FBI Until the End of the World 22 09/03/2010 11:22:16
Red Sun Compromise 21 09/03/2010 11:08:57
Preet Whos going to love you live 20 09/03/2010 10:51:22
The Ambersons Oh, my Isabelle 19 09/03/2010 10:51:22
VK Lynne Find Me 18 09/03/2010 10:45:33
Clara Barker Wishing 17 09/03/2010 10:43:45and so on. I am not that experienced with sql queries but if someone could point me in the right direction on this it would be very much appreciated. Is it some sort of grouping that i need to use? Regards Ray
For a start I am just a little bit confused. If you wanted the top 10 most played songs, then surely it doesn't matter if 2 songs have been played the same amount of times. If you really want to do it this way, then you need to specify DISTINCT on your count_played field i.e
SELECT DISTINCT countplayed, artist, title FROM ... etcHope this helps [EDIT] To limit the resultset to the top 10 would depend on your database.SELECT TOP 10 ...would work with access and SQL Server, but with MySql you need to goSELECT DISTINCT count_played FROM songlist ORDER BY count_played DESC LIMIT 10modified on Tuesday, March 9, 2010 8:18 AM
-
Hello there I have a table which contains the following fields... artist title count_played date_title_played I am trying to figure out how to present a top ten most played, but I can't seem to get my head around how to stop it showing duplicate count_played. At the moment I have used the following Select...
SELECT top 10 artist, title, count_played, date_title_played
FROM dbo.songlist
WHERE count_played >0
ORDER BY count_played DESC, date_title_played DESCHowever this is what shows...
Artist Title Count Last Played
Wanted By The FBI Until the End of the World 22 09/03/2010 11:22:16
Koko Conley Keep Your Head Up 22 09/03/2010 10:35:07
Red Sun Compromise 21 09/03/2010 11:08:57
Cara Friend of Mine 21 09/03/2010 11:01:09
Preet Whos going to love you live 20 09/03/2010 10:51:22
The Ambersons Oh, my Isabelle 19 09/03/2010 10:51:22
Patchwork Grace Boozawaffle 19 09/03/2010 10:49:01
VK Lynne Find Me 18 09/03/2010 10:45:33
Clara Barker Wishing 17 09/03/2010 10:43:45
Red Sun Just To Hold You 17 09/03/2010 10:11:00What I actually want is...
Artist Title Count Last Played
Wanted By The FBI Until the End of the World 22 09/03/2010 11:22:16
Red Sun Compromise 21 09/03/2010 11:08:57
Preet Whos going to love you live 20 09/03/2010 10:51:22
The Ambersons Oh, my Isabelle 19 09/03/2010 10:51:22
VK Lynne Find Me 18 09/03/2010 10:45:33
Clara Barker Wishing 17 09/03/2010 10:43:45and so on. I am not that experienced with sql queries but if someone could point me in the right direction on this it would be very much appreciated. Is it some sort of grouping that i need to use? Regards Ray
Try a sub query that finds max [Last Played] by Count. select ... from songlist inner join (select count, max([last played])... If you need more let me know. djj
-
For a start I am just a little bit confused. If you wanted the top 10 most played songs, then surely it doesn't matter if 2 songs have been played the same amount of times. If you really want to do it this way, then you need to specify DISTINCT on your count_played field i.e
SELECT DISTINCT countplayed, artist, title FROM ... etcHope this helps [EDIT] To limit the resultset to the top 10 would depend on your database.SELECT TOP 10 ...would work with access and SQL Server, but with MySql you need to goSELECT DISTINCT count_played FROM songlist ORDER BY count_played DESC LIMIT 10modified on Tuesday, March 9, 2010 8:18 AM
Hi Thank you for your help, I am using sql 2000 however if I use DISTINCT the results seem to stay the same, if I only use the count_played field then yes it shows only single rows of a particular count number but then how do I include the artist and title fields? I may not be seeing the wood for the trees as I don't really know much sql sorry. Regards Ray
-
Try a sub query that finds max [Last Played] by Count. select ... from songlist inner join (select count, max([last played])... If you need more let me know. djj
Sorry I was pressed for time. The sub query should be something like:
SELECT top 10 A.artist, A.title, A.count_played, B.MaxDate
FROM dbo.songlist A
INNER JOIN (
SELECT count_played, MAX(date_title_played) AS MaxDate
FROM dbo.songlist
GROUP BY count_played
) AS B
ON A.date_title_played = B.MaxDate
WHERE count_played >0
ORDER BY count_played DESC, date_title_played DESCThis most likely will not run but again as this is of the top of my head. The join condition (ON) may need work.
-
Sorry I was pressed for time. The sub query should be something like:
SELECT top 10 A.artist, A.title, A.count_played, B.MaxDate
FROM dbo.songlist A
INNER JOIN (
SELECT count_played, MAX(date_title_played) AS MaxDate
FROM dbo.songlist
GROUP BY count_played
) AS B
ON A.date_title_played = B.MaxDate
WHERE count_played >0
ORDER BY count_played DESC, date_title_played DESCThis most likely will not run but again as this is of the top of my head. The join condition (ON) may need work.
Sorry so long with this reply I also get pressed with work time unfortunately. I tried the suggested query and have now got it running fine, for this I thank you, however I do have a small issue and can't seem to find the answer to it. If 2 songs exist with the same name i.e. "only you" by the flying pickets which has 47 plays "only you" by the platters which has 14 plays It will show both even though 14 plays does not fit with the top 5 most played I slightly altered you code to fit my needs as follows...
SELECT top 5 A.artist, A.title, A.count_played, B.MaxDate FROM dbo.songlist A INNER JOIN (SELECT count_played, MAX(date_title_played) AS MaxDate
FROM dbo.songlist GROUP BY count_played ) AS B ON A.date_title_played = B.MaxDate
WHERE B.count_played >0
ORDER BY B.count_played DESC, date_title_played DESCAny ideas ? Thanks in advance
-
Sorry so long with this reply I also get pressed with work time unfortunately. I tried the suggested query and have now got it running fine, for this I thank you, however I do have a small issue and can't seem to find the answer to it. If 2 songs exist with the same name i.e. "only you" by the flying pickets which has 47 plays "only you" by the platters which has 14 plays It will show both even though 14 plays does not fit with the top 5 most played I slightly altered you code to fit my needs as follows...
SELECT top 5 A.artist, A.title, A.count_played, B.MaxDate FROM dbo.songlist A INNER JOIN (SELECT count_played, MAX(date_title_played) AS MaxDate
FROM dbo.songlist GROUP BY count_played ) AS B ON A.date_title_played = B.MaxDate
WHERE B.count_played >0
ORDER BY B.count_played DESC, date_title_played DESCAny ideas ? Thanks in advance
Try a sub query (top 5) that is grouped by title and ordered by number of plays. Let me think about this and hopefully get back to you soon. djj