SQL ROW_NUMBER

The following code snippet will identify the MAX value on a given dimension(s), based on the value of a specified field.  This works well if you are trying to determine which value of a given dimension that you would like to display, if there are multiple options.

By Definition:  Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.  You can then specify which row number you would like to see in your result set.

The actual SQL code is quite simple:

 

For simplicity, say you have the following data, which contains weekly NFL Player fantasy football stats (Running Backs), for 2 years (Weeks 1-3).  To view the entire table, execute the SQL code snippet above to view entire contents of the temp table #PlayerRank

SQL ROW_NUMBER

 

Now, let’s say you’d like to see the top player (by FPTS) for each week, over the 2 year period?  By running the code snippet above, you will get the following results:

SQL ROW_NUMBER

 

Update the code only display the top player (by FPTS) for each year, over the 2 year period, is easy.  Simply comment out the PARTITION BY for NFLWeek, and the query will dimension only by NFLYear:

Results:

SQL ROW_NUMBER

 

The SQL ROW_NUMBER is handy when you are trying to isolate specific instances of a given dimension(s), based on the content of a specified field.  You can modify this macro to meet your needs.

 

Additional Content

Check out more examples by visiting my Home Page

Here you will find topics covering  Qlikview  SQL Server  Excel VBA

 

2 thoughts on “SQL ROW_NUMBER

    • Matt, I have added a CREATE TABLE script in the TSQL code block above, which creates a Temp table. The post now uses the content of this Temp table. Hope this helps. Feel free to post any further questions.

Leave a Comment