CODEWITHSUNDEEP
sqlsql-server-2005
Jack Mills
Jack Mills

Reputation: 6132

Selecting Nth Record in an SQL Query

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

Upvotes: 8

Views: 44891

Answers (12)

Pravin Kumar Mishra
Pravin Kumar Mishra

Reputation: 87

try This

Let us assume , We want select 5th row of WC_Video Table And 

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

Upvotes: 0

Vinothkumar Savaridoss
Vinothkumar Savaridoss

Reputation: 1

Select from tablename limit nthrow,1;

Upvotes: 0

Harendra Negi
Harendra Negi

Reputation: 11

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

Upvotes: 1

Johnno Nolan
Johnno Nolan

Reputation: 29659

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
)

Upvotes: 10

AlejandroR
AlejandroR

Reputation: 5191

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

Upvotes: 1

shahkalpesh
shahkalpesh

Reputation: 33476

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.

Upvotes: 1

Paulo Santos
Paulo Santos

Reputation: 11567

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.

Upvotes: 1

Degan
Degan

Reputation: 989

How about

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

Upvotes: 4

rein
rein

Reputation: 33445

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

Upvotes: 1

Ben S
Ben S

Reputation: 69342

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.

Upvotes: 0

Ben Hughes
Ben Hughes

Reputation: 14185

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

OFFSET is a good thing for MySQL

Upvotes: 2

John Saunders
John Saunders

Reputation: 161773

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;

Upvotes: 2

Related Questions