How to Select the Nth Row in a SQL Database 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 to select the n'th last element in database

There is no such thing as the nth last row in a table. In SQL tables represent unordered sets.

If you have a column that specifies the ordering, you can use:

select t.*
from t
order by <ordering column> desc
limit 1 offset n - 1;

In SQLite, tables are often created with rowid as a column that is often used to represent the insert order (although that might not always be true). That may be what you want to use as the ordering column.

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;

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
)

SQL: How to select 1st, 3rd, 11th and nth row from a table?

If there is a primary key defined for the table that is an integer based data type--both MySQL and SQLite have auto_increment for example--then you can use:

SELECT t.*
  FROM TABLE t
 WHERE t.id IN (1,3, 11)

...where id is the auto_increment column.

There's very little detail to go on, but MySQL and SQLite do not have analytic query support, making queries rather complicated:

SELECT y.*
  FROM (SELECT t.*,
                        (SELECT COUNT(*)
                            FROM TABLE x
                           WHERE x.col <= t.col) AS rank
               FROM TABLE t) y
 WHERE y.rank IN (1, 3, 11)

---

Related Topics