Quake3World

Posted: **Sat Jun 02, 2007 12:29 pm**

I have two tables. One table "songs" with a list of titles from songs. Each song also has a unique ID number.
In another table, called "favorites", I store which user has which song listed as favorite. So this table contains records which two attributes, "userid", which references to users (not relevant for this question) and "songid", which references to a song.

Now, what I want to do is, figure out which are the most popular songs (the songs that have the most records in the favorites table). I could simply do

SELECT count(songtitle) AS favCount, songid
FROM favorites
GROUP BY songid
ORDER BY count(game) DESC;

Which gives me the most popular songid's. Now, obviously, I want to know not the songids, but the songtitles (from table songs). I've tried various things with left joins but nothing seems to work. What makes it even worse is that I'm stuck to MS Access.

Can anyone help me write a query that does what I want?

Posted: **Sat Jun 02, 2007 12:33 pm**

Pho will know this one.

Posted: **Sat Jun 02, 2007 12:46 pm**

select S.name
from songs S, favorites F
where S.song_id = F.song_id
and F.user_id = ?

Posted: **Sat Jun 02, 2007 12:47 pm**

you might need a 'SELECT DISTINCT(songtitle)' in there.

Posted: **Sat Jun 02, 2007 1:05 pm**

select count(*), songtile
from songs s, favs f
where s.id = f.songid
group by songtitle

Posted: **Sat Jun 02, 2007 1:10 pm**

Shouldn't this do the trick?

Code: Select all

SELECT COUNT(*) AS count, s.title
FROM favorites f, songs s
WHERE s.songid = f.songid
GROUP BY f.songid
ORDER BY count DESC

Posted: **Sat Jun 02, 2007 1:16 pm**

And just for kicks, the LEFT JOIN version (I didn't use the original field names though):

Code: Select all

SELECT COUNT(*) AS count, s.title
FROM favorites f
LEFT JOIN songs s ON s.songid = f.songid
GROUP BY f.songid
ORDER BY count DESC

Posted: **Sat Jun 02, 2007 3:03 pm**

To have the query return the actual most popular song you need to use one of these.

Code: Select all

SELECT routename
FROM troute
where routeid = (SELECT top 1 assignedto
	FROM tevent
	GROUP BY assignedto
	order by count(assignedto) desc)


SELECT top 1 routename
FROM tevent
inner join troute on troute.routeid=tevent.assignedto
GROUP BY routename
order by count(assignedto) desc

According to Query Analyzer, the first query is 1% faster in our local database and 4% faster in the production database. So, the second way may look cleaner but outer/inner joins on large tables are extremely costly.

Posted: **Sun Jun 03, 2007 6:46 pm**

^misantropia^ wrote:Shouldn't this do the trick?
Code: Select all
SELECT COUNT(*) AS count, s.title
FROM favorites f, songs s
WHERE s.songid = f.songid
GROUP BY f.songid
ORDER BY count DESC

I thought that should do it as well. It gives me the error "you tried to execute a query that does not include the specified expression 'songtitle' as part of an aggregate function."

Posted: **Mon Jun 04, 2007 3:03 pm**

Eraser wrote:
^misantropia^ wrote:Shouldn't this do the trick?
Code: Select all
SELECT COUNT(*) AS count, s.title
FROM favorites f, songs s
WHERE s.songid = f.songid
GROUP BY f.songid
ORDER BY count DESC
I thought that should do it as well. It gives me the error "you tried to execute a query that does not include the specified expression 'songtitle' as part of an aggregate function."

If you want that version just replace the group by f.songid with s.title.

Quake3World

SQL question

SQL question