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