Selecting Specific Row Number in SQL

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 specific row number in sql

For SQL Server 2005+ (set @startRow and @endRow):

SELECT OrderingColumn 
FROM (
    SELECT OrderingColumn, ROW_NUMBER() OVER (ORDER BY OrderingColumn) AS RowNum
    FROM MyTable
) AS MyDerivedTable
WHERE MyDerivedTable.RowNum BETWEEN @startRow and @endRow

SQL fiddle example: http://sqlfiddle.com/#!3/b4b8c/4

How to select a row based on its row number?

A couple of the other answers touched on the problem, but this might explain. There really isn't an order implied in SQL (set theory). So to refer to the "fifth row" requires you to introduce the concept

Select *
From 
(
    Select 
      Row_Number() Over (Order By SomeField) As RowNum
    , *
    From TheTable
) t2
Where RowNum = 5

In the subquery, a row number is "created" by defining the order you expect. Now the outer query is able to pull the fifth entry out of that ordered set.

How to select a specific row from a set of rows in SQL

You should be able to get the result you want by finding all CLM_NBR values that are not also a READMIT_CLM_NBR (so they are the original admission); these rows can then be ordered by DATE_ADMITTED and the row with the latest date selected:

WITH CTE AS (
    SELECT READMIT_CLM_NBR, CLM_NBR, DATE_ADMITTED,
           ROW_NUMBER() OVER (ORDER BY DATE_ADMITTED DESC) AS rn
    FROM P_Admission p1
    WHERE MBR_ID = '0610297305'
      AND NOT EXISTS (SELECT * FROM P_Admission p2 WHERE p2.READMIT_CLM_NBR = p1.CLM_NBR)
) 
SELECT READMIT_CLM_NBR, CLM_NBR, DATE_ADMITTED
FROM CTE
WHERE rn = 1

Demo (for MySQL 8 but generic SQL) on db-fiddle

SQL select single row number x only

If you want 4 rows, then use order by and top - or a fetch clause:

select top(4) t.*
from mytable t
order by id

Or:

select t.*
from mytable t
order by id
offset 0 rows fetch first 4 rows only

You can then iterate through the resultset in your application.

If, on the other hand, you want the 4th row only, then just change the fetch clause:

select t.*
from mytable t
order by id
offset 3 rows fetch next 1 row only

Add a row number to result set of a SQL query

The typical pattern would be as follows, but you need to actually define how the ordering should be applied (since a table is, by definition, an unordered bag of rows). One way to do that if you don't care about a specific order otherwise is to use the leading key(s) of a covering index, the leading key(s) of the clustered index, or the columns in any group by / order by clauses. In this case I'll assume A is the single-column clustering key for t:

SELECT t.A, t.B, t.C, number = ROW_NUMBER() OVER (ORDER BY t.A)
  FROM dbo.tableZ AS t
  ORDER BY t.A;

If you truly don't care about order, you can generate arbitrary/nondeterministic row numbering using:

ROW_NUMBER() OVER (ORDER BY @@SPID)

-- or for serial plans

ROW_NUMBER() OVER (ORDER BY @@TRANCOUNT)

Little tricks I picked up from Paul White in this article (see "Paul's Solution").

Not sure what the variables in your question are supposed to represent (they don't match).

Selecting rows that have row_number more than 1

You can use window functions:

select t.* except (cnt)
from (select t.*,
             count(*) over (partition by id) as cnt
      from t
     ) t
where cnt > 1;

As applied to your aggregation query:

SELECT iym.* EXCEPT (cnt)
FROM (SELECT id, year, month, 
             SUM(sales) as sales, 
             ROW_NUMBER() OVER (Partition by id ORDER BY id ASC) AS row_number
             COUNT(*) OVER(Partition by id ORDER BY id ASC) AS cnt
      FROM table
      GROUP BY id, year, month
     ) iym
WHERE cnt > 1;

---

Related Topics