How to Number Rows in SQL Database: Standard SQL PostgreSQL Oracle MS SQL Server MySQL SQLite Operators: ROW_NUMBER OVER ORDER BY ASC DESC Table of Contents Problem: Example: Solution: Discussion: Problem: You would like to give each row in the result table a separate number. Example: Our database has a table named furniture with data in the following columns: code and name. The furniture table stores the name of pieces of furniture that we want to number. codename 101bed 202sofa 333chair 123bookcase 235table 766desk Solution: SELECT ROW_NUMBER() OVER() AS num_row, name, code FROM furniture; The query returns the row number of each piece of furniture along with its name and code: num_rownamecode 1bed101 2sofa202 3chair333 4bookcase123 5table235 6desk766 Note that the names of the pieces of furniture are not sorted. Discussion: If you’d like to number each row in a result set, SQL provides the ROW_NUMBER() function. This function can be used in the SELECT clause with other columns. After the ROW_NUMBER() function, we use the OVER() clause. If you don't pass in any arguments to OVER(), the numbering of rows will not be sorted according to any column. Thus, the order of the displayed rows will be non-deterministic; in most cases, it is the order in which the records were inserted into the table. The numbering of rows starts at 1. In our example, each record has a number from 1 to 6. We can also assign row numbers with reference to a specific column. In the example below, we number records sorted by name. We do this by passing in that argument to OVER() along with ORDER BY clause. This query sorts records according to the name column: SELECT ROW_NUMBER() OVER(ORDER BY name) AS num_row, name, code FROM furniture; As you look at the result set, you see the sorted rows with their numbers: rownamecode 1bed101 2bookcase123 3chair333 4desk766 5sofa202 6table235 The numbering provided by ROW_NUMBER() is independent of the order of rows in the result table. In the example below, we number records using the sorted column name (OVER(ORDER BY name)), but we display records in the result set according to another column (in our example, ORDER BY code). SELECT ROW_NUMBER() OVER(ORDER BY name) AS num_row, name, code FROM furniture ORDER BY code; The query returned the number each row, but these numbers are not in ascending order, because records are sorted according column code: rownamecode 1bed101 2bookcase123 5sofa202 6table235 3chair333 4desk766 The default order of the sorted rows is ascending, but you can also sort in descending order using the DESC keyword after the name of the column by which you want to sort: SELECT ROW_NUMBER() OVER(ORDER BY name ASC) AS num_row, name, code FROM furniture ORDER BY code DESC; The query above returned the result set: rownamecode 4desk766 3chair333 6table235 5sofa202 2bookcase123 1bed101 Recommended courses: Window Functions Window Functions Practice Set Recommended articles: SQL Window Functions Cheat Sheet 11 SQL Window Functions Exercises with Solutions How to Use Rank Functions in SQL SQL Window Functions By Explanation SQL Window Function Example With Explanations See also: How to Add Rankings with DENSE_RANK() in SQL How to Rank Rows Within a Partition in SQL What’s the Difference Between RANK and DENSE_RANK in SQL? How to Eliminate Duplicate Rows in SQL How to Find Duplicate Rows in SQL? How to Count Distinct Values in SQL Subscribe to our newsletter Join our monthly newsletter to be notified about the latest posts. Email address How Do You Write a SELECT Statement in SQL? What Is a Foreign Key in SQL? Enumerate and Explain All the Basic Elements of an SQL Query