Search content within the blog

Friday, April 17, 2009

RowNumber In SQL Server

SELECT * FROM dbo.Customers ORDER BY LastName, FirstName
This query above will display the following results:



Now we'll make a slight modification to add a "RowNumber" field.
SELECT
ROW_NUMBER() OVER (ORDER BY LastName, FirstName) AS RowNumber,
*
FROM
dbo.Customers

This syntax may look a little odd, but it's not that difficult. First of all, the ROW_NUMBER() function (just like all other 'ranking functions' in SQL SERVER 2005) needs an OVER clause. Notice that we moved the ORDER BY clause inside the OVER clause. Basically what this is doing is telling SQL how we want the ROW_NUMBER() function to number each record. So we are telling SQL to number the records 1, 2, 3, 4 in the order of LastName then FirstName. Here are the results:



So now that we have a "RowNumber" field, we can add a simple WHERE clause to get only a limited number of records. We will have to wrap query in another SELECT statment so that we can use all of our dynamic columns (such as our RowNumber column) by their names that we assigned them. Also, I'm going to use the TOP clause to limit the query to only return 2 records at a time. Lets see the query, and the results:
SELECT TOP 2 * FROM (SELECT
ROW_NUMBER() OVER (ORDER BY LastName, FirstName) AS RowNumber,
*
FROM
dbo.Customers) _myResults
WHERE
RowNumber > 2

Notice I 'aliased' the first query and called it "_myResults". This basically treats that inner query as a VIEW so that I could pull out that first column by it's name, RowNumber. Here are the results:

No comments:

Post a Comment