1

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?

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
iamal
  • 159
  • 2
  • 15
  • Possible duplicate of [T-SQL How to select only Second row from a table?](https://stackoverflow.com/questions/3617152/t-sql-how-to-select-only-second-row-from-a-table) – Tab Alleman Apr 25 '18 at 14:39

4 Answers4

4

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.
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
1

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

php_node
  • 35
  • 4
1

We can use TOP clause for this -

SELECT TOP (1) Col1 FROM (Your SQL Query) as XYZ Order By Col3 DESC
DatabaseCoder
  • 2,004
  • 2
  • 12
  • 22
0

You can try

SELECT * FROM
(
   SELECT r.*, ROW_NUMBER() OVER (ORDER BY SomeField ASC) AS RowNum
) sorty
WHERE RowNum = 2
Sandeep
  • 333
  • 2
  • 7
  • can I create an array and store more than one value in a single variable? My select statement was returning more than one value and I need to store it in a variable. How can I do that? – iamal Apr 26 '18 at 11:34
  • use `table` variable. – Sandeep Apr 26 '18 at 11:36