ROW_NUMBER function in SQL Server

Written on May 18, 2016

ROW_NUMBER() is one of windows function in sql server, used to current number of row in result set based on order by statement and sometimes based on order by within particular group.

Its syntax is as follows

ROW_NUMBER() OVER ( [PARTITION BY statements] ORDER BY statements )

Let consider scenario, we have query to get name of products with quantity within inventory for specific locations. We want to add row number for each record returned. Its final query will be as below.

Here, we have generated row_number based on descending order of quantity.

Suppose we want to get row_number for each location on descending order of quantity, its query will be as below.

Here, as you can analyze result, we are now having row_number partitioned by locationId.