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 :
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 :
for more details visit my sqlfiddle at : http://sqlfiddle.com/#!3/fe5d8/27
Don’t forget to share your comments. Thanks 🙂
A very good article. special thanks for your example given here
Thank you 🙂
Great article
Thank you 🙂
Thanks Bhavin..
It’s very useful.. 🙂
Thank you 🙂