January 16, 2012 1 Comment
Last week I was asked by a colleague, Sander (Blog | @SanderKooij), “What’s the easiest way of adding a unique identifier (or row ID) to a result set?”. That’s an interesting question. There are several ways to do this. For example, you could insert the resultset into a temp table (physical or memory temp table), with an extra column (Identity). But what if you want to do this in a Select statement itself? Here’s a solution.
If you use the scripts I’ve added to this post (check Code Samples), you’ve created a table with country abbreviation codes. This table contains the data as shown below:
If you select the data, and want to add a record ID to your result set, you can use the following script:
SELECT DefaultAbbreviation, CountryProper, CountryNumber, ROW_NUMBER() OVER(ORDER BY DefaultAbbreviation ASC) AS RowNumber FROM CountryAbbreviationCodes
The statement above uses the ROW_NUMBER built-in function to generate a row number for the result set. The ORDER BY clause in the functions is used to tell SQL Server what column(s) to use, to create a row number. In this case, the result set is ordered by the abbreviation column.
But what if you want to select the data with a specific row number? If you try to use the ROW_NUMBER function in the where clause, you get the following error:
“Windowed functions can only appear in the SELECT or ORDER BY clauses.”
So you need another solution. From SQL Server 2005 onwards we can use a Common Table Expression (CTE). With a CTE you can use a select statement as a table. So if you want to return the rows that are numbered 50 through 60, you can use the following query:
WITH OrderedCountries AS ( SELECT DefaultAbbreviation, CountryProper, CountryNumber, ROW_NUMBER() OVER(ORDER BY DefaultAbbreviation ASC) AS RowNumber FROM CountryAbbreviationCodes ) SELECT DefaultAbbreviation, CountryProper, CountryNumber, RowNumber FROM OrderedCountries WHERE 1 = 1 AND RowNumber BETWEEN 50 AND 60
The result of this statement is the 10 rows we requested. So with a CTE we can use the ROW_NUMBER function to return specific rows from a table.
With the ROW_NUMBER functions, you can also delete duplicate records from your tables. If interested, post a comment, mail or tweet me, and I might write a blog post about it.