Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Select nth result from a query

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.

like image 452
JakeJ Avatar asked Jul 13 '12 12:07

JakeJ


People also ask

How do you select nth value in SQL?

SELECT * FROM Employee; Now let's display the Nth record of the table. Syntax : SELECT * FROM <table_name> LIMIT N-1,1; Here N refers to the row which is to be retrieved.

How does one select every nth row from a table?

Here's the SQL query to select every nth row in MySQL. mysql> select * from table_name where table_name.id mod n = 0; In the above query, we basically select every row whose id mod n value evaluates to zero.

How do I select a specific value from a column in SQL?

The syntax is: SELECT column1, column2 FROM table1, table2 WHERE column2='value'; In the above SQL statement: The SELECT clause specifies one or more columns to be retrieved; to specify multiple columns, use a comma and a space between column names.


2 Answers

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.

like image 54
DataDeer.net Avatar answered Sep 24 '22 07:09

DataDeer.net


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

like image 21
Martin Smith Avatar answered Sep 26 '22 07:09

Martin Smith