ROW_NUMBER in SQL SERVER

Hello friends, here comes new post about ROW_NUMBER in SQL SERVER . It is easy to use in sql. I am explaining it in detail. We have already explained regarding RANK() ,NTILE()DENSE_RANK().

ROW_NUMBER in SQL SERVER returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.

Syntax : ROW_NUMBER() OVER (PARTITION BY column_name ORDER BY column_name ASC/DESC)

PARTITION BY :
Divides the result set into partitions to which the ROW_NUMBER function is applied.If PARTITION BY is not specified, the function treats all rows of the query result set as a single group.

ORDER BY :
The ORDER BY clause determines the sequence in which the rows are assigned their unique ROW_NUMBER within a specified partition.

Lets take a example of sales table. Now if i want to generate the row number for the sales table without using any partition based on the order of the country then the query will be.

SELECT
ROW_NUMBER() OVER (ORDER BY S.COUNTRY) AS ‘ROW NUMBER’,
S.COUNTRY, S.FIRSTNAME, S.LASTNAME,S.PRODUCT,S.PRICE FROM SALES S

Output :

ROW_NUMBER_1-300x192

Now lets take a different approach. If i want to generate the row number in the group of country where each country group will start with number 1 then the query will be.

SELECT
ROW_NUMBER() OVER (PARTITION BY S.COUNTRY ORDER BY S.COUNTRY) AS ‘ROW NUMBER’,
S.COUNTRY, S.FIRSTNAME, S.LASTNAME, S.PRODUCT,S.PRICE FROM SALES S

Output :

ROW_NUMBER_2-300x190

for more details visit my sqlfiddle at : http://sqlfiddle.com/#!3/fe5d8/27

Don’t forget to share your comments. Thanks 🙂

6 comments

Leave a Reply