CODEWITHSUNDEEP
databasesybase
Dan
Dan

Reputation: 1262

Selecting rows between x and y from database

I've got a query which returns 30 rows. I'm writing code that will paginate those 30 rows into 5 records per page via an AJAX call.

  1. Is there any reason to return just those 5 records up the presentation layer? Would there be any benefits in terms of speed or does it just get all the rows under the hood anyways?

  2. If so, how do I actually do it in Sybase? I know Oracle has Rownum and MS Sql has something similar, but I can't seem to find a similar function in Sybase.

Upvotes: 1

Views: 2547

Answers (5)

Nick Larsen
Nick Larsen

Reputation: 18877

I agree completely with jmgant, however, if you want to do it anyway, the process goes something like this:

  1. Select top 10 items and store in X
  2. Select top 5 items and store in Y
  3. X-Y

This entire process can happen in 1 SQL statement.

Upvotes: 0

Gratzy
Gratzy

Reputation: 9389

I think in sybase you can use 

select top 5 * from table 
where order-by-field > (last record of previous calls order-by-field)  
order by order-by-field

just make sure you use the same order by each time.

As for benefit I guess it depends on how many rows we are talking and how big the table is etc.

Upvotes: 0

MusiGenesis
MusiGenesis

Reputation: 75376

I've never worked with Sybase, but here's a link that explains how to do something similar:

http://www.dbforums.com/sybase/1616373-sybases-rownum-function.html

Since the solution involves a temp table, you can also use it for pagination. On your initial query, put the 30 rows into a temporary table, and add a column for page number (the first five rows would be page 1, the next five page 2 and so on). On subsequent page requests, you query the temp table by page number.

Not sure how you go about cleaning up the temp table, though. Perhaps when the user's session times out?

For 30 records, it's probably not even worth bothering with pagination at all.

Upvotes: 0

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171539

Some info here:

Selecting rows N to M without Oracle's rownum?

Upvotes: 0

John M Gant
John M Gant

Reputation: 19308

Unless your record length is huge, the difference between 5 and 30 rows should be completely unnoticeable to the user. In fact there's a significant potential the multiple DB calls will harm performance more than help. Just return all 30 rows either to your middle tier or your presentation, whatever makes more sense.

Upvotes: 3

Related Questions