In this video we will discuss Row_Number function in SQL Server.
Row_Number function
- Introduced in SQL Server 2005
- Returns the sequential number of a row starting at 1
- ORDER BY clause is required
- PARTITION BY clause is optional
- When the data is partitioned, row number is reset to 1 when the partition changes
Row_Number function without PARTITION BY : In this example, data is not partitioned, so ROW_NUMBER will provide a consecutive numbering for all the rows in the table based on the order of rows imposed by the ORDER BY clause.
SELECT Name, Gender, Salary,
ROW_NUMBER() OVER (ORDER BY Gender) AS RowNumber
FROM Employees![sql server row_number example](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj2F6k3i1eQvjdGCeQyMZ5i3SEL5wPYg3Ph2n9JsAOdYeXCSUUaIeT5Q5cgOcKLxZ0pSmo5JsaV9lpFnL3hPg01H85hP7oub8_nTRFHFCgX22IZ6-m5N4nrnvERjzjxR65pvwJI7RGTQT0/s1600/sql+server+row_number+example.png)
Please note : If ORDER BY clause is not specified you will get the following error
The function 'ROW_NUMBER' must have an OVER clause with ORDER BY
Row_Number function with PARTITION BY : In this example, data is partitioned by Gender, so ROW_NUMBER will provide a consecutive numbering only for the rows with in a parttion. When the partition changes the row number is reset to 1.
SELECT Name, Gender, Salary,
ROW_NUMBER() OVER (PARTITION BY Gender ORDER BY Gender) AS RowNumber
FROM Employees![sql server row_number example](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgp9w0onK3uzafo_RQSJBZqObMfxwxFhPKcLFSqykyTlfTPRFkCweFZ3veo8IuXi5nlzTF1IOxAcAAhvt3WHsObpNis5mr8X9DhMZnfAjkx07vhc54rjNyMrti51AxWvZVa7sAct1xXKZY/s1600/sql+server+row+number+by+partition.png)
Use case for Row_Number function : Deleting all duplicate rows except one from a sql server table.
0 comments:
Post a Comment
Note: only a member of this blog may post a comment.