SQL ROW_NUMBER

Row_Number () is used to create a sort column in the record list that results from a sql query. In short, the rotating list is a function that creates line numbers for sorting. We can also group these line numbers by any field. This function comes with SQL 2005.

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

It is imperative to use the Order By statement. Using Partition by is optional.

---

Examples

I’ve defined an increased value with ROW_NUMBER to the people in the “Customers” tabs. I’ve written these values ​​in the “ROW NUMBER” column.

SELECT cus.first_name, cus.last_name, 
ROW_NUMBER() OVER(ORDER BY cus.customer_id) AS [ROW NUMBER]
FROM BikeStores.sales.customers as cus;

SQL ROW_NUMBER

---

If I want to group people in the “Customers” table as a family, I have to use “PARTITION BY”.

SELECT cus.first_name, cus.last_name, 
ROW_NUMBER() OVER(PARTITION BY cus.last_name ORDER BY cus.customer_id) AS [ROW NUMBER]
FROM BikeStores.sales.customers as cus;

SQL ROW_NUMBER

---

For example, if we want to return the 3rd person in a family, we write the code as follows:

SELECT * FROM(
SELECT cus.first_name, cus.last_name, 
ROW_NUMBER() OVER(PARTITION BY cus.last_name ORDER BY cus.customer_id asc) AS [ROW NUMBER]
FROM BikeStores.sales.customers as cus
)
k
WHERE [ROW NUMBER] = 3;

SQL ROW_NUMBER

---

SELECT * FROM(
SELECT cus.first_name, cus.last_name, 
ROW_NUMBER() OVER(PARTITION BY cus.last_name ORDER BY cus.customer_id desc) AS [ROW NUMBER]
FROM BikeStores.sales.customers as cus
)
k
WHERE [ROW NUMBER] = 3;

SQL ROW_NUMBER