Skip to content
Advertisements

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
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
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
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
SQL ROW_NUMBER
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: