SQL Server : is there any alternate for TOP clause?

Question

Possible Duplicate:
Equivalent of LIMIT and OFFSET for SQL Server?

I am working on a table which currently contains 2500 rows, and will be much more and more in future.

I am using jqgrid to display the records from the database .

Whenever the grid is loading, I am retrieving the top 500 rows from the database. However when I am started using pagination in the grid, the number of records is adding.

Example : first the number of rows is TOP 100, then going onto the next page its increased to TOP 200 and so on in the query.

Now I want a solution to avoid this issue such that I can limit the rows instead of retrieving top n rows based on the grid. Say using a range or LIMIT

Example

When I am navigating to next page in grid, instead of TOP 200 rows I want to fetch rows from 101 to 200

 select top 500
        exe.id,
        bat.BName,
        bat.tid,
        bat.freq,
        exe.status,
        exe.Msg,
        exe.time,
        exe.Fi
    from
       XXX exe,
        YYY bat 
    where
        exe.id=bat.id   
    order by     
        CONVERT(VARCHAR(10),
        exe.time,
        120) DESC,
        exe.status,
        exe.id DESC

Please guide me in this, as I am new to jQGrid and not much aware how to handle this DB related stuff

I also referred few links in Stackoverflow but can't understand

Thanks in advance

you have this tagged with both sql-server and oracle which RDBMS do you want a solution for? — Taryn, Aug 23 '12 at 11:01

score 6 · Accepted Answer · edited Aug 23 '12 at 13:08

6

You can use ROW_NUMBER(), the row_number() is not assigned until you query the data so if you want to retrieve the data by the row_number() value, then you will need to use CTE or a sub-select to get the value for use in a WHERE clause.

SELECT *
FROM
(
  select  exe.id,
        bat.BName,
        bat.tid,
        bat.freq,
        exe.status,
        exe.Msg,
        exe.time,
        exe.Fi,
        row_number() over(order by CONVERT(VARCHAR(10), exe.time, 120) DESC,
                   exe.status, exe.id DESC) rn
  from XXX exe,
       YYY bat 
  where exe.id=bat.id  
) x
WHERE rn between 101 and 200

I would also suggest a change in your JOIN syntax to use ANSI syntax:

SELECT *
FROM
(
  select  exe.id,
        bat.BName,
        bat.tid,
        bat.freq,
        exe.status,
        exe.Msg,
        exe.time,
        exe.File,
        row_number() over(order by CONVERT(VARCHAR(10), exe.time, 120) DESC,
                   exe.status, exe.id DESC) rn
  from XXXexe
  INNER JOIN  YYY bat 
     ON exe.id=bat.id  
) x
WHERE rn between 101 and 200

edited Aug 23 '12 at 13:08

John Conde

217,595
99
455
496

answered Aug 23 '12 at 11:04

Taryn

242,637
56
362
405

thank you , so first time am retrieving 500 records , from the next time i have to pass values dynamically to between clause right ? – Esh Aug 23 '12 at 11:11
You can create a stored proc that you just pass in the parameters for each set that you want to retrieve. No need to return 500 records if you don't want them all. :) – Taryn Aug 23 '12 at 11:12
@john conde thanks for the edit, I guess I didn't realize I cut off the first character when copy/pasting. :) – Taryn Aug 23 '12 at 13:09
@bluefeet, better a first character in "You" then missing a character in a query! – John Conde Aug 23 '12 at 13:11

SQL Server : is there any alternate for TOP clause?

1 Answers1