How to Select a Row Based on Its Row Number

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.

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

Get a row number on select statement while matching entire row

When you run this query:

SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS SNO, t.*
FROM [table1] t;

The SNO values are unstable. That means that the same query run multiple times might return different numbers. Sorting in SQL is not stable. That means that identical keys can be in an arbitrary order when the query is run multiple times. Why? SQL tables and result sets represent unordered sets. There is nothing to base a stable sort on.

The simplistic answer to your question is to use a subquery:

SELECT t.*
FROM (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS SNO, t.*
      FROM [table1] t
     ) t
WHERE [Total Sales] = 2555;

However, the real answer is that you should be using multiple columns to create a stable sort, if you want to use this value for more than one query.

Use row_number() to take middle row in a set of records

Edit: Updated to use a cte.
Edit2: Updated to use a join rather than a sub-select to handle multiple castingIds

Since ROW_NUMBER() is going to give us a continuous set of values, why not find MAX(RN), divide by 2 and then round? If the MAX(RN) is odd, you'll get the true median, if it's even you'll get rounded down. There's likely a cleaner way to do this but something like this:

  WITH cte AS (
        SELECT 
            temperatureID 
            ,castingID
            ,temperatureValue
            ,ROW_NUMBER() OVER (PARTITION BY castingID ORDER BY TemperatureDateTime) AS RN
        FROM Temperatures
)
SELECT
    * 
FROM cte AS c
INNER JOIN (
    SELECT
        castingID
        ,CEILING(CONVERT(DECIMAL(7,2),MAX(RN)) / 2) AS med
    FROM cte
    GROUP BY castingID
) AS m ON c.rn = m.med AND c.castingID = m.castingID

Here is a SQL Fiddle with the result of the query:
https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=5b3aebf3ab4ced946c90a435d9edce3c

There's three use cases (all with different castingID).

1.) Odd number of rows

2.) Even number of rows

3.) A single row

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;

select data.table R rows based on row number and condition

Here are my two attempts toward the solution: The first one uses the summarize syntax of data.table to calculate a logical vector using row number .I and condition at position i for subsetting and updating columns; the second one uses which and setdiff to remove certain row numbers from the condition, if on the other hand you need an and operation of row number and condition, setdiff can be replaced with union:

x[x[, .I != 2 & V2 > 2], V3 := "more"]
x
#    V1 V2   V3
# 1:  a  1   NA
# 2:  b  2   NA
# 3:  c  3 more
# 4:  d  4 more

x[setdiff(which(V2 > 2), c(2)), V3 := "more"]
x
#    V1 V2   V3
# 1:  a  1   NA
# 2:  b  2   NA
# 3:  c  3 more
# 4:  d  4 more

---

Related Topics