Rownum/Limit clause substitute

[javaRocks]

Hi Experts!
I am looking for a way to select 15 rows say, from rownum=5 to 20-how do I do it in MS-SQL? in MySQL we do limit and in Oracle it would be rownum. Any substitutes/workarounds?
Thanks a million!
Vedu

 

[SQLSister]

There is no rownumber feature in SQL server. REcordsets are by definition not ordered and can be returned ina differnet order whenever you selct them unless you use an order by clause.

YOu should never consider doing processing based on what row the record is in as that will change with time. YOu need some kind of ID field to identify records. Then you can use order by and the where clasue to get the records you want.

 

[JamesLean]

You can use TOP to limit the number of rows returned, normally in conjunction with an ORDER BY clause. eg to return the top 10 highest paid employees:

SELECT TOP 10 empid, empname, salary
FROM employees
ORDER BY salary DESC

To return a specific range use a construct something like this:

SELECT TOP 10 empid, empname, salary
FROM employees
WHERE empid NOT IN
  (
  SELECT TOP 10 empid
  FROM employees
  ORDER BY salary DESC
  )
ORDER BY salary DESC

(This will return range 11 to 20)

--James

 

[krotha]

Hi JamesLean
I have a similar problem, This works fine for me. But I want put this select statement in a procedure and make 10 as a variable and it gives me syntax error.

alter procedure page_split
@page_number int,
@page_size int
as
declare @previous_pages int
Begin
select @previous_pages = @page_size * (@page_number - 1)

select TOP @page_size work_request_no
from work_request
WHERE work_request_no NOT IN
(
SELECT TOP @previous_pages work_request_no
FROM work_request
ORDER BY work_request_no
)
ORDER BY work_request_no
End
GO

If i made a dynamic sql it works fine.