How to Get the Nth Row in a SQL Server Table

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.

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.

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

How to get every nth row value in sql

You can use ROW_NUMBER:

SELECT [Year] 
FROM (
  SELECT [Year], ROW_NUMBER() OVER (ORDER BY [Year] ASC) rn 
  FROM table_name
  GROUP BY [YEAR]
) t WHERE (t.rn - 1) % 3 = 0

demo on dbfiddle.uk

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
)

Update the nth row on a SQL Server table

Use UserId(s) as nth row.because, UserId(s) starts from 1 and looks auto incremental.

in order to update rows that their position is in multiple of 5 use below query

UPDATE dbo.[User]
SET    SelectedForPublicViewSwitch = 1 
WHERE  UserId IN (
    SELECT UserId 
    FROM #t2_NotSelected 
    WHERE  UserId % 5 = 0 // Multiple of 5
    )

Above query returns rows that are multiple of 5 from #t2_NotSelected table like: 5 10 15 20 ... and updates values of SelectedForPublicViewSwitch to 1 in the table of User

SQL - Get Nth row based on other variable

You can use the row_number() window function to get row numbers for recoreds as ordered by datetime partitioned by driver_id.

SELECT id,
       ...
       trip_status
       FROM (SELECT id,
                    ...
                    trip_status,
                    row_number() OVER (PARTITION BY driver_id
                                       ORDER BY datetime) rn
                    FROM elbat
                    WHERE trip_status = 'completed')
       WHERE rn = 3;

---

Related Topics