SQL stands for Structured Query Language, which is a computer language used to store, manipulate, and retrieve data from a relational database. SQL is a database management language that supports database creation, deletion, obtaining rows, and altering rows, among other features.

In this tutorial, we will be learning about one such feature of SQL i.e rownum, and how it is used in SQL.

Post Graduate Program in Business Analysis

In partnership with Purdue UniversityVIEW COURSE
Post Graduate Program in Business Analysis

SQL ROW_NUMBER Function

ROW_NUMBER function is a SQL ranking function that assigns a sequential rank number to each new record in a partition.

When the SQL Server ROW NUMBER function detects two identical values in the same partition, it assigns different rank numbers to both.

The rank number will be determined by the sequence in which they are displayed.

SQL ROW_NUMBER Syntax

The syntax for ROW_NUMBER function in SQL is as follows-

ROW_NUMBER() OVER (

    [PARTITION BY expr1, expr2,...]

    ORDER BY expr1 [ASC | DESC], expr2,...

)

Now, let us look at the different clauses used in the syntax above.

OVER

This clause specifies the window or set of rows that the window function operates. The PARTITION BY and ORDER BY are the two possible clauses of the OVER clause.

PARTITION BY

It is an optional clause in the ROW_NUMBER function. It is a clause that divides the result set into partitions (groups of rows). The ROW_NUMBER() method is then applied to each partition, which assigns a separate rank number to each partition.

If the partition by clause is not specified, the ROW NUMBER function will treat the entire result as a single partition and rank it from top to bottom.

ORDER BY

Within each partition, this clause allows us to order the rows in the result set. Because the ROW NUMBER() function is order-dependent, it is a necessary clause.

Free Course: Introduction to SQL

Learn MySQL, PostgreSQL and SQL ServerEnroll Now
Free Course: Introduction to SQL

How to Use ROW_NUMBER in SQL Query?

Now that we have covered the basic syntax for writing the ROW_NUMBER function in SQL, let us look at an example to understand how it works with a SQL server table.

Example

For understanding how the ROW_NUMBER function works, we will use the Employee table as given below.

rownum_in_SQL_1

The given statement uses the ROW_NUMBER() to assign a sequential number to each employee in the table above.

SELECT

ROW_NUMBER() OVER (

ORDER BY E_id

) row_num,

E_name

FROM

Employee;

Output

rownum_in_SQL_2

How to Use the SQL ROW_NUMBER Function With PARTITION?

The following statement uses the ROW_NUMBER function to sequentially assign an integer to each employee in the table. The partition clause has been used here to divide the different employees based on their country.

SELECT

   E_name,

   E_country,

   ROW_NUMBER() OVER (

   PARTITION BY E_country

   ORDER BY E_name

   ) row_num

FROM

   Employee

ORDER BY

   E_country;

Output

rownum_in_SQL_3

As you can see in the output above that each employee has been divided based on the country. The sequentially assigned integer, or ROW_NUMBER reinitializes every time the name of the country is changed.

Business Analyst Master's Program

Gain expertise in Business analytics toolsExplore Program
Business Analyst Master's Program

How to Return a Subset of Rows Using CTE and ROW_NUMBER?

In applications, the ROW NUMBER() method is used for pagination. You can, for example, present a list of customers by page, with 10 rows each page.

The given code statement shows the use of ROW_NUMBER function to return employee rows from 1 to 5.

CTE is defined as "common table expression." A CTE allows you to create a named temporary result set that is available in the execution scope of SELECT, INSERT, UPDATE, DELETE, or MERGE statements.

WITH cte AS (

SELECT

     ROW_NUMBER() OVER(

          ORDER BY

             E_name

     ) row_num,

     E_id,

     E_name

  FROM

     Employee

) SELECT

E_id,

E_name

FROM

cte

WHERE

row_num > 1 AND

row_num <= 5;

Output

rownum_in_SQL_4.

In the example above, CTE uses the ROW NUMBER() method to assign a sequential integer to each row in the result set.

Second, the outer query returned the rows with row numbers ranging from 1to 5.

Gain expertise in the latest Business analytics tools and techniques with the Post Graduate Program in Business Analysis. Enroll now!

Conclusion

In this article we learned about the ROW_NUMBER function in SQL and how is it used in different situations with partition and cte to obtain results according to our need. If you wish to learn more about SQL and get certified, checkout Simplilearn's SQL certification training

Further, if you have any doubts, please feel free to drop them in the comments section and our experts will help you out.

About the Author

SimplilearnSimplilearn

Simplilearn is one of the world’s leading providers of online training for Digital Marketing, Cloud Computing, Project Management, Data Science, IT, Software Development, and many other emerging technologies.

View More
  • Disclaimer
  • PMP, PMI, PMBOK, CAPM, PgMP, PfMP, ACP, PBA, RMP, SP, and OPM3 are registered marks of the Project Management Institute, Inc.