CODEWITHSUNDEEP
sqlsql-server
iamal
iamal

Reputation: 149

How to select only the second result from a SQL query

I'm using SQL Server. I have an SQL Query which returns more than one row and I need to select only the 2nd result from it or the first result may be. What query should I write for this requirement?

Upvotes: 1

Views: 10020

Answers (5)

Reda_user250
Reda_user250

Reputation: 1

SELECT t.*
FROM table_name t
OFFSET 1 ROW
LIMIT 1

If you want the second record in a sorted list, this will work.

The offset will bump it off one row (according to sort). The limit 1 will return only 1 row.

Upvotes: -2

DatabaseCoder
DatabaseCoder

Reputation: 2032

We can use TOP clause for this -

SELECT TOP (1) Col1 FROM (Your SQL Query) as XYZ Order By Col3 DESC

Upvotes: 1

php_node
php_node

Reputation: 33

SELECT * FROM TABLE ORDER BY COLUMN ASC LIMIT 1 TO FETCH FIRST ROW

SELECT * FROM TABLE ORDER BY COLUMN DESC LIMIT 1 TO FETCH LAST ROW

Upvotes: 0

Sandeep
Sandeep

Reputation: 353

You can try

SELECT * FROM
(
   SELECT r.*, ROW_NUMBER() OVER (ORDER BY SomeField ASC) AS RowNum
) sorty
WHERE RowNum = 2

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269623

You can use offset/fetch:

select t.*
from t
order by col
offset 1 fetch next 1 row only;

Notes:

  • A SQL result set returns rows in an indeterminate order, unless you have an order by. Even running the query twice on the same system and return the rows in different order, so you need the order by.
  • The offsets start at 0 for the first row. For the second row, offset 1.
  • offset/fetch is ANSI standard syntax.

Upvotes: 6

Related Questions