Selecting the nth record in a SQL query

Question

I have an SQL query that I am running, but I only want to select a specific row. For example, let's say my query:

Select * from Comments 

Suppose this returns 10 rows, I only want to select the eighth record returned by this query. I know that I can:

 Select Top 5 * from Comments 

To get the 5 best records of this query, but I only want to select a specific record, is there anything that I can contribute to this query to do this (seems to be the top).

thanks

Nest

Answer 1

This is a classic interview question.

In Ms SQL 2005+, you can use the keyword ROW_NUMBER () and have 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 can do something like

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

Answer 2

What about

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

Answer 3

First, you have to say which RDBMS you use.

Secondly, you should carefully consider what you are trying to accomplish. Relational databases are set-based. In the general case, the order of the elements in the set does not matter. You want to ask why this is important in this case, and then see if there is a better way to embed the concept of an order in the request itself.

For example, in SQL Server 2005 (and other DBMSs) you can use the ROW_NUMBER function to assign a sequence number to each row returned based on the criteria you specify. Then you can select the rows based on the row number. An example from a book on the Internet:

 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 4

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

OFFSET is a good thing for MySQL

Answer 5

Well, in T-SQL (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 

Thus you get the 8th record.

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

I read the question, and your comments on you will require the following 3 blog comments, etc.

How are your tables structured?
Suppose you have a blog id and a comment id is generated in ascending order for each blog post, you can make SELECT based on the current id.

eg. if blogpostId = 101, you get a list of the 3 best comments by the posted id. Now let's say you want the following 3 comments - you can make a SELECT WHERE commentId between the last comment identifier specified in the comment identifier - 3

But it all depends on how your tables are defined.

Answer 8

In SQL 2000, where you do not have the ROW_NUMBER () function, you can use a workaround like:

 SELECT CommentsTableFieldList, IDENTITY(INT, 1,1) as seqNo INTO #SeqComments FROM Comments SELECT * FROM #SeqComments WHERE seqNo = 8 

Answer 9

 select top 1 * from TableName where ColumnName1 in ( select top nth ColumnName1 from TableName order by ColumnName1 desc ) order by ColumnName1 desc 

Answer 10

In the SELECT link, 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 devices may have a different keyword.

Answer 11

 Select from tablename limit nthrow,1; 

Answer 12

try this one

Suppose we want to select the 5th row of the table WC_Video AND

 Select * from (Select Row_Number() over (Order by Uploadedon) as 'rownumber',* from Wc_Video )as Temp where rownumber=5