Sort data loaded into a temporary table in SQL (SQL Server)

Icon of SQL

Purpose: to load data into a temporary table in SQL (SQL Server) and sort the records by a column’s values.

Showing data sorted by two variables
Output from a temporary table in SQL
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’

Syntax description

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’

Syntax description

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'

Worked example

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.