SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    Irritability Defined
    Join Date
    Jul 2000
    Location
    80,000 feet below the surface
    Posts
    1,442
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SQL Query - pulling the top 10 most popular records

    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.
    My 2 Cents (or is that 2.2 Cents including GST?)

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,214
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Code:
    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

  3. #3
    Irritability Defined
    Join Date
    Jul 2000
    Location
    80,000 feet below the surface
    Posts
    1,442
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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 :/
    My 2 Cents (or is that 2.2 Cents including GST?)

  4. #4
    Irritability Defined
    Join Date
    Jul 2000
    Location
    80,000 feet below the surface
    Posts
    1,442
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:

    PHP Code:
    select top 10 filenamecount(*) as downloads from dbname where filename between '10000' and '20000' group by filename order by 2 desc

    Cheers, BC>>
    Last edited by BC; May 27, 2003 at 19:15.
    My 2 Cents (or is that 2.2 Cents including GST?)

  5. #5
    SitePoint Wizard
    Join Date
    Jan 2001
    Location
    Milton Keynes, UK
    Posts
    1,011
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

    Code:
    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

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,214
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    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

  7. #7
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,249
    Mentioned
    112 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by r937
    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:

    Code:
    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)
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,214
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    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



Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •