Ranking Functions, TOP and Aggregates

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