SQL Calculating the row number

//

To label the row number in SQL there’s a useful SQL function you need to know about:

ROW_NUMBER()

As the name suggests, it numbers all rows. But it’s a lot more powerful than that.

Using the Partition and Order clauses it’s possible to segment and group your data with specific rules.

Note this is different to the SQL rank function.

Show the Top N in SQL

The ROW_NUMBER() function is the way to select the top X rows by specified criteria.

For example, the top 10 selling products per country or the top 5 sales people per region.

The syntax of this is :

ROW_NUMBER() OVER ( [ <partition_by_clause> ] <order_by_clause> )

Personally I only find the syntax examples useful with examples. (For more details you can see the row_number function on the Microsoft website here.

Here is some demo data to run through some common scenarios. Run this code in SQL Server Management Studio (SSMS) to create the test data.


CREATE TABLE #Sales
    (
    Country varchar(10)
    ,Person varchar(2)
    ,Sales decimal (18,2)
    )

insert into #Sales values ('UK','AW',8329)
insert into #Sales values ('UK','KS',542)
insert into #Sales values ('UK','NE',942)
insert into #Sales values ('UK','LT',7358)
insert into #Sales values ('UK','AP',4262)
insert into #Sales values ('France','ME',952)
insert into #Sales values ('France','DD',8802)
insert into #Sales values ('France','WQ',2331)
insert into #Sales values ('France','PD',544)
insert into #Sales values ('France','ML',2418)
insert into #Sales values ('Italy','MN',5426)
insert into #Sales values ('Italy','DN',7982)
insert into #Sales values ('Italy','IA',499)
insert into #Sales values ('Italy','EY',1034)
insert into #Sales values ('Italy','LA',5429)

Example 1: Number each row with the highest selling person at the top

This is quite simple, you just need to order by sales in the Over part of the clause.


select Person
    ,Country
    ,Sales
    ,ROW_NUMBER() over (order by sales desc) as RowNumber
from #Sales

Example 2: Number each row with the highest selling person at the top doing this for each country

Slightly more complex, this is where we introduce the Partition By into to Over part of the clause


select Country
    ,Person
    ,Sales
    ,ROW_NUMBER() over (partition by country order by sales desc) as RowNumber
from #Sales

Example 3: Only select the top 3 sales people from each country in term of sales amount

Here we also need to use the same code as Example 2 and we just query that result set


select Country
    ,Person
    ,Sales
    ,RowNumber
from
    (
    select Country
        ,Person
        ,Sales
        ,ROW_NUMBER() over (partition by country order by sales desc) as RowNumber
    from #Sales
    )a
where RowNumber < 4
order by Country, RowNumber

Quite straightforward once you understand the syntax.

Note the Row_number() function can’t be used in the WHERE or HAVING clause, hence I had to use it as a subquery in Example 3.

Leave a Comment