Selecting Nth Record in an SQL Query

How to select the nth row in a SQL database table?

There are ways of doing this in optional parts of the standard, but a lot of databases support their own way of doing it.

A really good site that talks about this and other things is http://troels.arvin.dk/db/rdbms/#select-limit.

Basically, PostgreSQL and MySQL supports the non-standard:

SELECT...
LIMIT y OFFSET x 

Oracle, DB2 and MSSQL supports the standard windowing functions:

SELECT * FROM (
  SELECT
    ROW_NUMBER() OVER (ORDER BY key ASC) AS rownumber,
    columns
  FROM tablename
) AS foo
WHERE rownumber <= n

(which I just copied from the site linked above since I never use those DBs)

Update: As of PostgreSQL 8.4 the standard windowing functions are supported, so expect the second example to work for PostgreSQL as well.

Update: SQLite added window functions support in version 3.25.0 on 2018-09-15 so both forms also work in SQLite.

Selecting Nth Record in an SQL Query

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
)

How to extract the nth row of a sql statement?

Are you using Postgres Or SQLServer? Both have a ROW_NUMBER() window function, but the syntax, especially around subqueries, may be slightly different. I'll assume Postgres.

You query looks good to me, except that the rownum = 2 in your WHERE should presumably be rownumber = 2, since that's what you aliased the ROW_NUMBER() column as. You also presumably don't need the RewardType and rownumber columns in your result, since their values will always be 'Electronics' and 2 respectively. Corrected and formatted for readability, this looks ok to me:

SELECT RewardID, Name, Description, Image, RewardType, price
FROM (
    SELECT
        ROW_NUMBER() OVER(ORDER BY RewardID ASC) AS rownumber,
        RewardID,
        Name,
        Description,
        Image,
        RewardType,
        price
    FROM Reward) AS num
WHERE RewardType = 'Electronics' and rownumber = 2

The only questionable part of that is the WHERE RewardType = 'Electronics'. Should that actually be in the subquery rather than the outer query? The difference is that if it is in the subquery, the row counting will include only reward type 'Electronics', whereas in the outer query, all reward types will be counted. To only count the reward type 'Electronics', modify it as so:

SELECT RewardID, Name, Description, Image, RewardType, price
FROM (
    SELECT
        ROW_NUMBER() OVER(ORDER BY RewardID ASC) AS rownumber,
        RewardID,
        Name,
        Description,
        Image,
        RewardType,
        price
    FROM Reward
    WHERE RewardType = 'Electronics') AS num
WHERE rownumber = 2

Edit: Since the comment you just made clarifies what you're really trying to do, I'll add that you should NOT be making an individual query for each row of the data that you want. Whatever interface you are using to your database will have a way of iterating over a query result that uses multiple rows, and if that's what you really need, you should find out how to do that.

Select Every Nth Record From SQL

SELECT t1.DateCol,
       t1.Value
FROM yourTable t1
INNER JOIN
(
    SELECT MIN(DateCol) AS firstDate
    FROM yourTable
    GROUP BY FORMAT(DateCol, 'dd/MM/yyyy hh')
) t2
    ON t1.DateCol = t2.firstDate

If you instead wanted to group by every 15 minutes, you could try:

GROUP BY CONCAT(FORMAT(DateCol, 'dd/MM/yyyy hh'),
                FLOOR(DATEPART(MINUTE, DateCol) / 15))

Return the nth record from MySQL query

SELECT * FROM table ORDER BY ID LIMIT n-1,1

It says return one record starting at record n.

Selecting every nth row with a condition in SQL

you can give an unique no to each of the row, and then do the calculation on the unique no. Something like this

with t as(    
select *,row_number() over (order by event_ID) SN from the_table_name
)

select * from t where SN%100=0

How do I find the nth row from a sql query?

Use DENSE_RANK():

;WITH cte AS
(
    SELECT DENSE_RANK() OVER (ORDER BY Salary DESC) AS r, *
    FROM Employee e
)
SELECT *
FROM cte
WHERE r = 2

SQL Fiddle

---

Related Topics