SQL Query - pulling the top 10 most popular records

[BC]

Hi all,

I have a single table in MS SQL Server 2K with a number of columns, with the relevant ones being 'date' and 'filename'. A record is inserted every time someone downloads a ringtone, and stamped with the date and the filename (a 5-digit ID) of the ringtone.

Via SQL, I want to pull out the 10 most popular ringtones in the last month and order them by popularity (i.e., count and group the most amount of times a certain filename has been downloaded, and repeat the process for the other filenames, then pull the 10 most popular ones and display on the site). As a single table contains records for a separate month there's no need for nasty joins.

I've Googled and done all the tutes but can't find the answer - I suspect it has something to do with GROUP BY/DESC commands?

(Note: using PHP to display records)

Any help appreciated. Thanks.

[r937]

select top 10 
filename
, count(*) as downloads
from yourtable
where month(datedownloaded) = month(getdate())-1 
group by filename
order by 2 desc

i included the WHERE condition that it look only at last month's records

splitting separate months of data into separate tables is usually not necessary for sql server, which is an industrial strength database and scales well -- you wouldn't have to worry about handling this scaling issue this way yourself until you were talking about billions of records per month

also, a tip: do not use a reserved word like date to name a column

rudy

[BC]

Ahhhh the SQL Expert/God comes to the rescue! Many, many thanks Rudy. Works perfectly.

I agree with the assertions about separation of data + naming of columns, but unfortunately that was what I was given when I slipped into this job... A long term priority I'm afraid :/

[BC]

Next question: how do I work a BETWEEN clause in (i.e. display filename records between 10000 and 20000?)?

<<Edit: don't worry, worked it in. Full query for anyone interested is:

select top 10 filename, count(*) as downloads from dbname where filename between '10000' and '20000' group by filename order by 2 desc; 

Cheers, BC>>

[shane]

You could have a problem with Rudy's code as it doesn't take into account that the last month could be in the previous year. In January month(getdate())-1 would evaluate to 0 which'd match no records.

I've modified Rudy's code a bit and I think it should work.

DECLARE @Now	DATETIME
DECLARE	@Year	INT
DECLARE @Month	INT

-- Get month and year of last month
SET @Now = CURRENT_TIMESTAMP
SET @Year = YEAR(DATEADD(month, -1, @Now))
SET @Month = MONTH(DATEADD(month, -1, @Now))

SELECT TOP 10	[FileName],
		[DownLoadCount]	= COUNT(*)
FROM		<tablename>
WHERE		MONTH(DateDownloaded) = @Month
AND		YEAR(DateDownloaded) = @Year
GROUP BY 	[FileName]
ORDER BY 	[DownLoadCount] DESC

[r937]

thanks shane, you're absolutely right, i failed to check for january

less haste, more speed

i would not write it as t-sql, though, because then you couldn't, for example, declare it as a view, you would have to use it as a stored proc (not that this is a bad idea, it's just that stored procs don't have the same flexibility as views -- but that's a different thread)

i would instead just do calculations right in the WHERE clause, because if you do them correctly, they can be evaluated once, before the query loop

where year(DateDownloaded) = year(getdate())
and month(DateDownloaded) = month(getdate())-1
or year(DateDownloaded) = year(getdate())-1
and month(DateDownloaded) = 12
and month(getdate()) = 1

rudy

[DaveMaxwell]

where year(DateDownloaded) = year(getdate())
and month(DateDownloaded) = month(getdate())-1
or year(DateDownloaded) = year(getdate())-1
and month(DateDownloaded) = 12
and month(getdate()) = 1

Drawing a blank here, but aren't AND and ORs processed at the same time? If so you would be more secure in surrounding the appropriate portions within parenthesis to be sure that the criteria is clean and you get the result you want:

WHERE (year(DateDownloaded) = year(getdate())
		   AND month(DateDownloaded) = month(getdate())-1)
	  OR (year(DateDownloaded) = year(getdate())-1
			AND month(DateDownloaded) = 12
			AND month(getdate()) = 1)

[r937]

true enough, and i do advise people to use parentheses when in doubt, but compound conditions are evaluated with ANDs taking precedence over ORs, so in this particular case, the parens aren't required