How to get nth record in a sql server table without changing the order?(sql server)

Question

for example i have data like this(sql server)

id    name
4      anu
3      lohi
1      pras
2      chand

i want 2nd record in a table (means 3 lohi) if i use row_number() function its changes the order and i get (2 chand) i want 2nd record from table data

can anyonr please give me the query fro above scenario

question from:https://stackoverflow.com/questions/65901302/how-to-get-nth-record-in-a-sql-server-table-without-changing-the-ordersql-serv

Answer 1

There is no such thing as the nth row in a table. And for a simple reason: SQL tables represent unordered sets (technically multi-sets because they allow duplicates).

You can do what you want use offset/fetch:

select t.*
from t
order by id desc
offset 1 fetch first 1 row only;

This assumes that the descending ordering on id is what you want, based on your example data.

You can also do this using row_number():

select t.*
from (select t.*,
             row_number() over (order by id desc) as seqnum
      from t
     ) t
where seqnum = 2;

I should note that that SQL Server allows you to assign row_number() without having an effective sort using something like this:

select t.*
from (select t.*,
             row_number() over (order by (select NULL)) as seqnum
      from t
     ) t
where seqnum = 2;

However, this returns an arbitrary row. There is no guarantee it returns the same row each time it runs, nor that the row is "second" in any meaningful use of the term.