0

i want to select set of records in SQL Server table.

In SQL Server, I can do this:

select TOP 40,60 * 
from sometable
order by name

I want to get the 40th to the 60th rows.

Thank you.

EBS
  • 1,283
  • 2
  • 13
  • 16
  • Possible duplicate of http://stackoverflow.com/questions/470542/how-do-i-limit-the-number-of-rows-returned-by-an-oracle-query-after-ordering. – Raj Nov 07 '13 at 05:38
  • Actually - **no**; you cannot run this query in SQL Server - it doesn't know the `LIMIT` keyword, that's a MySQL-specific extension – marc_s Nov 07 '13 at 05:51
  • I think the question is wrongly tagged. `LIMIT` is MySql syntax and the question is about Oracle and neither are tagged. Nothing to do with SQL SERVER – Raj Nov 07 '13 at 06:05

4 Answers4

0

LIMIT exists in Oracle but with different arguments, the first argument specifies the offset of the first row to return, and the second specifies the maximum number of rows to return. The offset of the initial row is 0 (not 1): 

SELECT * FROM sometbl Order by name LIMIT 40,100;  # Retrieve rows 41-100

Or you can proceed as;

SELECT * FROM
(
   SELECT st.*, Row_Number() OVER (ORDER BY name) rn FROM sometable st
) 
WHERE rn BETWEEN 40 AND 60;
Nadeem_MK
  • 7,031
  • 7
  • 44
  • 56
0

Use Row_number function and fetch the rows you need 

WITH tmp AS  
    (SELECT *, ROW_NUMBER() OVER (ORDER BY a.name) AS 'rn', a.* FROM table a)
    SELECT * FROM tmp WHERE rn BETWEEN 40 AND 60
vhadalgi
  • 6,571
  • 4
  • 32
  • 66
0

Try this

SELECT * FROM
(
  SELECT table.*, Row_Number() OVER (ORDER BY name) rank FROM table 
) 
WHERE rank BETWEEN 40 AND 60;
Nitu Bansal
  • 3,646
  • 3
  • 16
  • 23
0

i did that like this query

WITH temp AS
( SELECT *,    ROW_NUMBER() OVER (ORDER BY name) AS 'RowNumber'
   FROM sometable 
) 
SELECT * FROM temp WHERE RowNumber BETWEEN 40 AND 60

Thank you for all comment.

EBS
  • 1,283
  • 2
  • 13
  • 16