CODEWITHSUNDEEP
sqlsql-server-2005t-sql
Azhar
Azhar

Reputation: 20670

How to make an SQL query to get some specific range of rows from a table

How to make an SQL query if there are 30 records in a table, and I want to pick rows from 12 to 20 where 12 and 20 are row numbers, not IDs?

IDs         Code
5           ABC 
6           SDF
8           WSA
10          FSD
15          IOP
.
.
.
.
80          AWS

Upvotes: 2

Views: 7245

Answers (3)

Pranay Rana
Pranay Rana

Reputation: 176896

If you are using MS SQL Server then the row_number() function is available.

Example:

USE AdventureWorks2008R2;
GO
WITH OrderedOrders AS
(
    SELECT SalesOrderID, OrderDate,
    ROW_NUMBER() OVER (ORDER BY OrderDate) AS 'RowNumber'
    FROM Sales.SalesOrderHeader 
) 
SELECT * 
FROM OrderedOrders 
WHERE RowNumber BETWEEN 50 AND 60;

Upvotes: 6

Sir Hally
Sir Hally

Reputation: 2358

It depends on your DBMS. ROW_NUMBER() is a standart way. But it isn't very fast and convinient. You can use special SQL-command.

For MSSQL SERVER. SELECT TOP(5)...

For MySQL. SELECT * FROM table LIMIT 0 , 30 (0 is a start position, 30 is a row count).

For Firebird. SELECT FIRST 10 ...etc.

Use http://en.wikipedia.org/wiki/Select_(SQL)#ROW_NUMBER.28.29_window_function, non-standard syntax.

Upvotes: 0

pdwalker
pdwalker

Reputation: 863

What SQL environment are you using? The answer will depend on the environment.

Also, how are the records ordered? Are they sorted in some fashion, or do you want them in natural order?

Looking at your example and assuming your ids column is the order you want them sorted in, you could use a query like this for MS SQL Server

select 
  top (20-12) ids, code 
from 
  [yourtable] 
where 
  ids in (SELECT TOP 20 id from stoplists order by id) 
order by 
  ID desc

e.g. grab the first X records you want, and then grab the records you want from that result set.

You can achieve the same things in other SQL syntaxes with a slightly different syntax.

Upvotes: 0

Related Questions