Selecting Nth Record in an SQL Query

Question

Hi,

I have an SQL Query that i'm running but I only want to select a specific row. For example lets say my query was:

Select * from Comments

Lets say this returns 10 rows, I only want to select the 8th record returned by this query. I know I can do:

Select Top 5 * from Comments

To get the top 5 records of that query but I only want to select a certain record, is there anything I can put into this query to do that (similar to top).

Thanks

jack

Answer 1

From the SELECT reference, use the LIMIT keyword:

SELECT * FROM tbl LIMIT 5,10;  # Retrieve rows 6-15
SELECT * FROM tbl LIMIT 5;     # Retrieve first 5 rows

Note: this is for MySQL, other SQL engines may have a different keyword.

Answer 2

This is a classic interview question.

In Ms SQL 2005+ you can use the ROW_NUMBER() keyword and have the Predicate ROW_NUMBER = n

USE AdventureWorks;
GO
WITH OrderedOrders AS
(
    SELECT SalesOrderID, OrderDate,
    ROW_NUMBER() OVER (ORDER BY OrderDate) AS 'RowNumber'
    FROM Sales.SalesOrderHeader 
)  

SELECT * 
FROM OrderedOrders 
WHERE RowNumber = 5;

In SQL2000 you could do something like

SELECT Top 1 *FROM
[tblApplications]
where [ApplicationID] In
(
    SELECT TOP 5 [ApplicationID]
    FROM [dbo].[tblApplications]
    order by applicationId Desc
)

Answer 3

Well, in T-SQL (the dialect for SQL Server) you can do the following:

SELECT TOP 1 *
  FROM (SELECT TOP 8 *
          FROM Table
         ORDER
            BY SortField)
 ORDER
    BY SortField DESC

This way you get the 8th record.

Answer 4

First, you should say which RDBMS you're using.

Second, you should give careful thought to what it is you're trying to accomplish. Relational Databases are set-based. In general, the order of elements in a set does not matter. You'll want to ask why it matters in this case, then see if there's a better way to embed the concept of order into the query itself.

For instance, in SQL Server 2005 (and other RDBMS), you can use the ROW_NUMBER function to assign a sequential number to each row returned, based on the criteria you specify. You could then select rows based on the row number. Example from Books Online:

USE AdventureWorks;
GO
WITH OrderedOrders AS
(
    SELECT SalesOrderID, OrderDate,
    ROW_NUMBER() OVER (ORDER BY OrderDate) AS 'RowNumber'
    FROM Sales.SalesOrderHeader 
) 
SELECT * 
FROM OrderedOrders 
WHERE RowNumber BETWEEN 50 AND 60;

Answer 5

SELECT * FROM comments WHERE ...conditions... LIMIT 1 OFFSET 8

OFFSET is a good thing for MySQL

Answer 6

For SQL Server 2005:

select rank() OVER (ORDER BY c.subject, c.date) as rank, c.subject, c.date
   from comments c
   where rank = 8

Answer 7

How about

SELECT TOP 1 * FROM 
   (SELECT TOP 8 * FROM Comments ORDER BY foo ASC)
ORDER BY foo DESC

Answer 8

As you can see, this is not a concept that RDBMSes are very comfortable with. Can you handle it in another abstraction layer?

Answer 9

I have read the question & your comments on you would want next 3 blog comments etc.

How is your tables structured?
Assume that you have blog post Id & comment Id is generated in ascending order for each blog post, you could do a SELECT based on the current Id.

e.g. if the blogpostId = 101, you get the top 3 comments order by posted Id. Now lets say, you want to get the next 3 comments - you could do a SELECT WHERE commentId between the last comment id shown TO the comment id - 3

But all that depends on how your tables are defined.

Answer 10

In SQL 2000 where you do not have ROW_NUMBER() function you could use a work-around like this:

SELECT CommentsTableFieldList, IDENTITY(INT, 1,1) as seqNo 
INTO #SeqComments 
FROM Comments

SELECT * FROM #SeqComments 
WHERE seqNo = 8