Share via

Ranking Functions, TOP and Aggregates

  • Article

This is something I came across a few times within the last few weeks so thought I’d share the findings:

You probably heard about ranking functions by now. However you may have a missed this great benefit ranking functions bring to easing a complex scenario: Combine TOP functionality with aggregations. Example; getting something like “top 3 most popular songs every day” out of the “requests” table on a jukebox can be challenging on a large table. Details follow: Assume a table with the following schema. This table records every individual requests played on the jukebox.

create table weblog.requests(

      id int identity primary key,

songid nvarchar(128) not null,

reqdate datetime default (getdate())

)

go

Clearly, there are other ways to do this. But a new a simpler way to do this would the following query:

WITH TopRequests(ROWNUM, REQCOUNT, SONGS, REQDAY)

AS

(

   SELECT ROW_NUMBER()

OVER (PARTITION BY datepart(dd,reqdate)

      ORDER BY datepart(dd,reqdate), COUNT(*) desc, songid) as [ROWNUM],

      COUNT(*) as [REQCOUNT],

      songid as [SONGS],

   DATEPART(dd,reqdate) as [REQDAY]

   FROM weblog.requests

   GROUP BY DATEPART(dd,reqdate), songid

)

SELECT REQDAY, SONGS, REQCOUNT

FROM TopRequests

WHERE ROWNUM<4

ORDER BY REQDAY, REQCOUNT desc, SONGS

Comments

  • Anonymous
    January 16, 2005
    I assume this isn't your SQL 2000 server, correct?
  • Anonymous
    January 17, 2005
    This is all SQL Server 2005.
  • Anonymous
    May 23, 2008
    This is something I came across a few times within the last few weeks so thought I’d share the findings: You probably heard about ranking functions by now. However you may have a missed this great benefit ranking functions bring to easing a complex scenario
  • Anonymous
    June 01, 2009
    PingBack from http://paidsurveyshub.info/story.php?id=74502