10

I'm using SQL Server 2012 and I was wondering if there is a way to select, for example, the third result from a query. I have looked into LIMIT and OFFSET but I'm not 100% sure if it applies to SQL Server, however I've been told that there is something to do what I want in SQL Server 2012.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
JakeJ
  • 1,381
  • 3
  • 14
  • 34
  • i think u want to return only n'th row by your query is its rigt – Renjith K N Jul 13 '12 at 12:27
  • @RenjithKN Exactly, from my query (Which could be simple or complex), I want to select the nth row from the results of the query I just ran – JakeJ Jul 13 '12 at 12:29
  • possible duplicate of [Row Offset in SQL Server](http://stackoverflow.com/questions/187998/row-offset-in-sql-server) – juergen d Jul 13 '12 at 12:31
  • 1
    @juergend This is specifically aimed at MSSQL 2012, that question was posted in 2011 and was aimed at any SQL version (No answers were aimed at 2012) – JakeJ Jul 13 '12 at 12:34
  • 3
    @juergend I would agree with the duplicate only if there were some way to emphasize the SQL Server 2012 answer. It's not the accepted answer but it is the best answer for SQL Server 2012 specifically. – Aaron Bertrand Jul 13 '12 at 12:36

3 Answers3

14
SELECT  *
FROM     YourTable
ORDER BY OrderingColumn ASC 
OFFSET  2 ROWS /*Skip first 2 rows*/
FETCH NEXT 1 ROWS ONLY

Note: You cannot use OFFSET ... FETCH without doing an ORDER BY first

Amber
  • 812
  • 8
  • 21
Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • Just got this to work myself, I knew it had something to do with offset but missed the fetch. Also, as a note, you cannot use OFFSET FETCH without doing an ORDER BY first – JakeJ Jul 13 '12 at 12:32
1

I would recommend

select * from table ORDER BY OrderingColumn ASC LIMIT n,1

This is a quirk of limit where if you give it a range, it only returns that range. It works in MySQL too.

DataDeer.net
  • 87
  • 1
  • 7
-1

Can’t you just select the second index? This is the way i would take say the third post in my table.

//index 3 $index3 = DB::query(‘SELECT * from posts’)[2];

Then you will have an array that is ready to go for you.

Perry Maholland
  • 9
  • 4