1

Hello I need a SQL query statement that gets me rows 'start' to 'finish'.

For example:

A website with many items where page 1 selects only items 1-10, page 2 has 11-20 and so on.

I know how to do this with Microsoft SQL Server and MySQL but I need an implementation that is platform independent. :/

I have an Increment line for IDs but deleting in-between will mess the result when I select via 

WHERE ID > number AND ID < othernumber

of course

Is this possible without fetching the whole database to a ResultSet?

Tim Schmelter
  • 450,073
  • 74
  • 686
  • 939
Ray Stanz
  • 81
  • 1
  • 8
  • Welcome to SO. Please provide your schema, sample code, what you have tried and an ice cream sundae. – Kermit Aug 28 '12 at 15:07
  • 1
    you can't write platform independent sql statements. – thavan Aug 28 '12 at 15:09
  • 1
    @thavan You can write standard SQL which should be fairly adaptable. – Kermit Aug 28 '12 at 15:12
  • All "platform-independent" solutions to this notorious problem have serious performance issues. That's why ANSI/ISO SQL added syntax specifically for it, but not all SQL platforms implement them yet. – RBarryYoung Aug 28 '12 at 15:23
  • @thavan: You absolutely can write platform-independent SQL statements, they just aren't very good most of the time. (What you *cannot* do is to write platform-independent SQL *Procedures*) – RBarryYoung Aug 28 '12 at 15:25

2 Answers2

1

I think your safest bet would be to use the BETWEEN operator. I believe it works across Oracle/MySQL/MSSQL.

WHERE ID BETWEEN number AND othernumber
Kermit
  • 33,827
  • 13
  • 85
  • 121
  • I guess that would be the safest one, I was just think for the case when first 100 IDs are gone I'll have to check further until there is something to fetch. Still better than selecting the whole database to a Result Set thank you. :) – Ray Stanz Aug 28 '12 at 19:38
  • @RayStanz You could always find the `MIN()` and `MAX()` and use those with `BETWEEN` (`WHERE ID BETWEEN (SELECT MIN(ID) + 100 FROM ...`) – Kermit Aug 28 '12 at 19:41
0

Concerning your comment " I was just think for the case when first 100 IDs are gone I'll have to check further until there is something to fetch", you might wanna consider NOT actually ever deleting stuff from your database but to add a flag like "active" or something like that to your tables so you can avoid situations like the one you're now trying to avoid. The alternative is where you are now, having to find the max and min rows in a filter

kolossus
  • 20,559
  • 3
  • 52
  • 104