Back to list Standard SQL How to Number Rows in SQL Database: SQL PostgreSQL Oracle SQL Server MySQL SQLite Operators:ROW_NUMBER, OVER, ORDER BY, ASC, DESC 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 (primary key) and name. codename 101bed 202sofa 333chair 123bookcase 235table 766desk The furniture table stores the name of pieces of furniture that we want to number. Solution: SELECT ROW_NUMBER() OVER() AS num_row, code, name 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 is used in a SELECT clause with other columns. After the ROW_NUMBER() clause, we call the OVER() function. If you 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 using a 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(it 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 in SQL Server SQL Practice Set Recommended articles: How to Use Rank Functions in SQL SQL Window Functions By Explanation A Gentle Introduction to Common SQL Window Functions See also: How to Eliminate Duplicate Rows in SQL How to Find Duplicate Rows in SQL? Tags: SQL PostgreSQL Oracle SQL Server MySQL SQLite Subscribe to our newsletter Join our weekly newsletter to be notified about the latest posts.