Purpose: to load data into a temporary table in SQL (SQL Server) and sort the records by a column’s values.
Topics on this page
SELECT BY statement in SQL
To select and view data from a database table in SQL (MS SQL Server Management Studio), and order the records (rows) by a column (variable), use the ‘ORDER BY’ statement. For example:
SELECT [field1] , [field2] , [field3] , [field4] FROM [db].[table1] WHERE [field1] = 'filter_value' ORDER BY [field4] --Records will be ordered by the values in [field4]
However, if the above script is used to load a SELECT statement into a temporary table, the ORDER BY function has no effect – the row order of the temporary table is determined by SQL.
Order data in a temporary table in SQL using ROW_NUMBER()
To get the desired sort order of records loaded into a temporary table in SQL, you can use the ROW_NUMBER() function.
Syntax: ROW_NUMBER() with order by
SELECT ROW_NUMBER() OVER (ORDER BY [Field] ASC) AS ‘Field_name’
ROW_NUMBER numbers the records in a result set, and the ordering can be based on the ascending or descending values in one or more of the Fields (columns / variables) in the data set; as with any SQL query, you are able to specify more than one column to order on. The ‘AS’ argument is where you name the resulting column.
Syntax: ROW_NUMBER() with partition and order by
In addition, the numbering can be ‘partitioned’ into subsets, starting at 1 for each different value in the column to be partitioned:
SELECT ROW_NUMBER() OVER (PARTITION BY [Category] ORDER BY [Value] ASC) AS ‘Sort_Order’
The ‘PARTITION BY’ argument defines the column (field) which will be used to create subsets; therefore in the example above, the records will be sorted by the values in the [Value] column, starting at 1 for each different value (=partition) in the [Category] column.
Example using ROW_NUMBER() below:
SELECT [field1] , [field2] , [field3] , ROW_NUMBER() OVER (ORDER BY [field4] ASC) AS 'Row_order' INTO #temp_table FROM [db].[table1] WHERE [field1] = 'filter_value'
The example below uses ROW_NUMBER() with PARTITION BY to create:
- a sort order of the result set based on values in the [Value] column, and
- grouped into subsets based on the [Category] column.
- and the resulting sort order values are output into the column we’ve named ‘Sort_Order’.
SELECT [ID] , [Category] , [Value] , ROW_NUMBER() OVER (PARTITION BY [Category] ORDER BY [Value] ASC) AS 'Sort_Order' INTO #temp_table FROM [db].[table1] WHERE [Category] = in ('A','B') --output the temporary table SELECT * FROM #temp_table
The result set
To get the same result each time using ROW_NUMBER()
The rows returned by a query using ROW_NUMBER() will be ordered exactly the same with each execution ONLY when the following conditions are met:
- The partitioned column values are unique;
- The ORDER BY column(s) values are unique;
- Combinations of values of the partition column and ORDER BY columns are unique.
Deterministic and non-deterministic meaning
ROW_NUMBER() is a non-deterministic function, which means that the results will vary unless certain conditions are met. Deterministic functions always return the same result each time that they are executed with a specific set of input values and given the same state of the database.