SQL Select query that returns a range

Question

Possible Duplicate:
Row Offset in MS SQL Server

I want to select a range from x1 to x2. Just like you can select the top results:

SELECT TOP X * FROM TABLE

SELECT TOP 5 * FROM tUsers

But I would like to select middle results. So if I want results 10-20 is there a way to query that?

SELECT 10-20 * FROM TABLE?

What Database distro are you using? (e.g. SQL Server, MySQL, etc) — Karl Nicoll, Apr 02 '11 at 21:00
Duplicate of: http://stackoverflow.com/questions/187998/row-offset-in-ms-sql-server, http://stackoverflow.com/questions/2135418/equivalent-of-limit-and-offset-for-sql-server — intgr, Apr 02 '11 at 21:00
@Karl: SQL Server, because other databases don't support the `SELECT TOP` syntax — intgr, Apr 02 '11 at 21:01
Sort of a duplicate. I looked before I posted and I think this one is phrased better. — Spidy, Apr 02 '11 at 21:03
@intgr - Haha, I missed that. I haven't dealt with anything other than SQL server for the mostpart, so I'm not well versed in the syntactic differences. Thanks! — Karl Nicoll, Apr 02 '11 at 21:11

score 15 · Accepted Answer · edited May 23 '17 at 12:07

15

With SQL Server :

Row Offset in SQL Server

With MySQL :

SELECT * FROM `your_table` LIMIT 10, 20

With Oracle :

SELECT * FROM `your_table` WHERE rownum >= 10 and rownum < 20;

With PostgreSQL :

SELECT * FROM `your_table` LIMIT 20 OFFSET 10

`your_table` must be replaced by your real table name

edited May 23 '17 at 12:07

Community

1
1

answered Apr 02 '11 at 21:00

Sandro Munda

39,921
24
98
123

The backticks are illegal in Oracle and PostgreSQL (actually in any database except MySQL) - and they are absolutely unnecessary even for the MySQL example – Apr 02 '11 at 21:13
\`your_table\` must be replaced by the real table name... – Sandro Munda Apr 02 '11 at 21:16
But the backticks are still illegal (and non-standard) – Apr 02 '11 at 21:17
1

@a_horse_with_no_name \`your_table\` must be replaced by the real table name !!! – Sandro Munda Apr 02 '11 at 21:20
1

And just one more thing, a comon error in Oracle 8 through 9i is to use RowNum like that (don't know if they've changed in more recent versions). Romnum is the last thing calculated by the DB, meaning that if you use Rownum > 1 it will always return no rows (since no row will ever fill the fist position, no other row will have rownum > 1. – Marcelo Myara Oct 11 '13 at 18:21
1

And just to be instructive about the issue mentioned above, you would have to select the rowNums on an inner select (so they would be calculated and returned to the outter-most select as regular data) and then, on an outter select, use it like that (rownum >= 10). Again, I'm only sure about this on version 8, 8i and 9i. – Marcelo Myara Oct 11 '13 at 18:23

score 5 · Answer 2 · answered Apr 02 '11 at 21:10

In SQL Server 2005 or above you can use a CTE and the ROW_NUMBER function:

WITH TblCte as
(
SELECT  *
        ,ROW_NUMBER() OVER (ORDER BY OrderCol) RowNumber
FROM    Table
)
SELECT  *
FROM    TblCte
WHERE   RowNumber between 10 and 20

In SQL Server 2000 or below, it was quite difficult and inefficient: http://social.msdn.microsoft.com/Forums/en-IE/transactsql/thread/e92d9b03-42ad-4ab9-9211-54215e7b9352

score 1 · Answer 3 · edited Aug 14 '12 at 05:39

1

In mysql this is

SELECT * FROM table LIMIT 10,20

edited Aug 14 '12 at 05:39

WhyNotHugo

9,423
6
62
70

answered Apr 02 '11 at 21:00

Toby Allen

10,997
11
73
124

score -2 · Answer 4 · answered Apr 02 '11 at 20:59

-2

What DB are you using? If you're on Oracle, try

where rownum >= 10 and rownum < 20;

answered Apr 02 '11 at 20:59

pgruetter

1,184
1
11
29

2

That won't work - the rownum must be evaluated with a derived table: select * from (select col_!, rownum as rn from your_table) where rn >= 10 and rn < 20; – Apr 02 '11 at 21:17

SQL Select query that returns a range

4 Answers4

Related