Ranking results in a subquery using ROW_NUMBER()

December 16th, 2018

I was recently looking at some data that I wanted to rank. The problem was I did not want to see all the data in the table ranked. I just wanted to see a subset of the data, but with each row ranked among the entire dataset. Let me layout an example to illustrate the situation.

Let us imagine a table that contains the average wait time for attractions at Magic Kingdom in Walt Disney World. Our data might look like this:

Now let us say we want to rank these attractions by WaitTime. We might execute this query:

SELECT ATTRACTION, ZONE, WAITTIME, ROWNUM
FROM ATTRACTIONS ORDER BY WAITTIME DESC;

We could expect to get these results back:

Rownum represents the order that the row was selected in the query, so lets assume that each row was selected in alphabetical order.

Now instead of ranking all the attractions across the entire park lets rank just the ones in Fantasyland. So now our query is:

SELECT ATTRACTION, ZONE, WAITTIME, ROWNUM 
FROM ATTRACTIONS WHERE ZONE = 'Fantasyland' 
ORDER BY WAITTIME DESC;

We could expect to get these results back:

Great! We have the attractions in Fantasyland ranked, but we have lost the information indicated how they rank against other rides in the other zones. This is where the window function ROW_NUMBER() comes in. Briefly, window functions are are SQL methods that calculate a single value for each row inside the “window” or a subset of data.

So if we try this query:

SELECT * FROM 
    SELECT ATTRACTION, ZONE, WAITTIME, ROW_NUMBER() 
        OVER(ORDER BY WAITTIME DESC)
        AS RANK FROM ATTRACTIONS)
WHERE ZONE = 'Fantasyland';

We should get:

Now we can see that the Seven Dwarfs Mine Train ride has the second highest wait time in the park. Thanks ROW_NUMBER()!

Window functions are really powerful when you are trying to do data analysis.

Check out this page SQL Window Functions Introduction to read more about them!