How to Return Record #n

Thu Jan 24, 2008 by Ajarn Mark Caldwell in musings-and-ramblings, best-worst-practices

I got this question in email a while ago, and it seems to come up quite a bit, which frankly surprises me. I guess it's because I almost never* write an application where I need to retrieve the 10th (or whatever number) record. I am always trying to retrieve the record for Joe Smith, or Order Number 1256487, or the last record entered on 12/31/2007, or... well, you get the point. I am frequently searching for records based on a data value. But I rarely, if ever, have had to go looking for the nth record.

I see this question often enough that it makes me wonder if it isn't a school assignment somewhere. It sounds like the type of question you would get in school (or maybe in an interview)... "Write a SQL statement that will return the 17th record in the table." Or maybe it comes from people using Access where they had the FIRST() statement...UGH!

Of course the problem with this is that there is no such thing as the first, or 17th or nth record by itself. There is no guarantee in a SQL Server database (nor probably any relational database) that records will be retrieved in a particular order without the use of an ORDER BY clause**. And if you cannot guarantee the order that records will be retrieved, then the ordinal position of a record is completely useless.

Now, after all of that, if you still think that you have a need to get the nth record, here is my suggestion. First add your ORDER BY clause. And second, read Graz's article entitled What's After TOP?

* The one exception is where I want the SQL Server to handle paging for me instead of my front-end system. If you are in that position, then I suggest you do a search on the term paging on the SQLTeam web site. There are numerous articles about how to accomplish this.

** OK, someone is likely going to point out that if you put a Clustered Index on the field that you want it to sort by, that it will retrieve the data in that order. For a single table retrieval, you may be right, but I wouldn't bet my career on that always working out, especially when joining other tables with clustered indexes. Also, you will end up in a world of hurt if you rely on that mechanism and down the road your Production DBA decides to redefine the clustered index onto different columns for overall system performance.

Legacy Comments


Hugo Kornelis 2008-01-26	re: How to Return Record #n Hi Ajarn, >>someone is likely going to point out that if you put a Clustered Index on the field that you want it to sort by, that it will retrieve the data in that order<< Not me. And if someone does, feel free to point them to the couterproof I gave in a blog post, a little over a year ago. The URL is http://sqlblog.com/blogs/hugo_kornelis/archive/2006/12/31/Beatles-vs-Stones.aspx Best, Hugo

Bill Dean 2008-01-27	re: How to Return Record #n Setup a column called ID as an integer, identity column in the main table being queried with auto-increment = 1. This will number the records so that you can query for "ID = n" as in: SELECT * FROM dbo.tblMain WHERE ID = n ORDER BY ID However, if your query restricts the records being returned such that all n records are not returned, then the nth record will either not be returned or won't be the nth record. The original post is correct in stating that normally queries should be written to compare against a key value.

Louis 2008-01-27	re: How to Return Record #n -- If you need to satisfy your needs (a Where Clause and an order By) you can try this select: -- Sql Server 2005 -- Database AdventureWorks -- Table HumanResources.Employee Declare @n int Declare @managerid int -- Choose a value @n for the nth # -- @managerid (or what ever you need in your Where statement) Select @n = 7 , @managerid = 21 -- Then run this select -- OBS: I used EmployeeID (PK in the table) in the Order By clause to differentiate -- Employees with the same BirthDate select top 1 * from ( Select top (select @n) * From HumanResources.Employee (nolock) Where ManagerID = @managerid Order By BirthDate, EmployeeID ) AS T Where (select Count(*) from HumanResources.Employee (nolock) Where ManagerID = @managerid) >= @n order by BirthDate Desc, EmployeeID Desc -- This select wil return the @n nth # (if it exists) depending -- on your Where clause and your Order By

Lood 2008-01-27	re: How to Return Record #n Or use the ROW_NUMBER function with a common table expression: WITH MyCTE AS ( SELECT ROW_NUMBER() OVER ( ORDER BY Rate DESC ) AS Ranking , FullName , Rate FROM employee SELECT * FROM MyCTE WHERE Ranking = 17

M.P. 2008-01-28	re: How to Return Record #n Louis, you complicated too much. You can easily simplify your query: SELECT TOP 1 * FROM HumanResources.Employee WHERE EmployeeID IN ( SELECT TOP 7 EmployeeID FROM HumanResources.Employee WHERE ManagerID = 21 ORDER BY BirthDate, EmployeeID ASC) ORDER BY EmployeeID DESC

Will 2008-01-28	re: How to Return Record #n SELECT * FROM dbo.tblMain WHERE ID = n ORDER BY ID This will only work with a static table. What if the 8th row is deleted? Then the desired row will have ID 11.

AjarnMark 2008-01-28	re: How to Return Record #n Good comments and responses to comments here. MP's solution to Louis' idea is basically the solution you will find explained in more detail in the What's After Top article referenced in my original post. In addition to the challenge that Will put up regarding Bill Dean's idea, there is also the concern that you may not be able to (or at least not advisable to) add columns to the table (in the case of 3rd-party software) or you may already have an IDENTITY column on the table in use for another purpose. But most of all, I was hoping that people would really dial-in on the faulty premise that there is such a thing as the Nth record. "Nth record" is ALWAYS relative. Without an ORDER BY clause, there is no guarantee that you will get records back in any particular order.

AjarnMark 2008-01-28	re: How to Return Record #n Hey, Lood, nice example, but I have to point out that even in your CTE you have an ORDER BY clause, so I think you still proved my main point.

SandeepArora 2008-02-03	re: How to Return Record #n Declare @RecordNumberYouWant int SET @RecordNumberYouWant = 1 Select * From HumanResources.Employee HE Where @RecordNumberYouWant = (Select count(*)+1 From HumanResources.Employee HE1 WHERE HE.EmployeeId > HE1.EmployeeId)

AjarnMark 2008-02-03	re: How to Return Record #n Sandeep, that's a nice trick, and an interesting use of an inequality, BUT, you made the ASSUMPTION that when I asked for the Nth record, I meant in the order they were inserted (you used the IDENTITY field). The best answer is to first ask the question, "Nth record based on what sort order?" Until you know the answer to that question, you don't know what fields to use in your inequality comparison. But your approach is definitely a handy trick to keep up your sleeve. Thanks for playing along!