How to Return Record #n

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.

posted @ Thursday, January 24, 2008 11:52 PM

Print

Comments on this entry:

# re: How to Return Record #n

Left by Hugo Kornelis at 1/26/2008 5:36 AM
Gravatar
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

# re: How to Return Record #n

Left by Bill Dean at 1/27/2008 2:04 AM
Gravatar
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.

# re: How to Return Record #n

Left by Louis at 1/27/2008 7:48 AM
Gravatar
-- 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

# re: How to Return Record #n

Left by Lood at 1/27/2008 11:23 PM
Gravatar
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

# re: How to Return Record #n

Left by M.P. at 1/28/2008 2:13 AM
Gravatar
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

# re: How to Return Record #n

Left by Will at 1/28/2008 7:28 AM
Gravatar
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.

# re: How to Return Record #n

Left by AjarnMark at 1/28/2008 9:32 PM
Gravatar
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.

# re: How to Return Record #n

Left by AjarnMark at 1/28/2008 9:41 PM
Gravatar
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.

# re: How to Return Record #n

Left by SandeepArora at 2/3/2008 12:38 PM
Gravatar
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)

# re: How to Return Record #n

Left by AjarnMark at 2/3/2008 9:16 PM
Gravatar
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!
Comments have been closed on this topic.
«August»
SunMonTueWedThuFriSat
272829303112
3456789
10111213141516
17181920212223
24252627282930
31123456