Win a copy of Re-engineering Legacy Software this week in the Refactoring forum
or Docker in Action in the Cloud/Virtualization forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

getting a range of returned rows?

 
Phil Chuang
Ranch Hand
Posts: 251
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I'm sure you've all seen this somewhere:
do a search on some data with some criteria, return results 10-20.
Is there a way to do this with SQL, or do you have to do this programmatically?
 
Ron Newman
Ranch Hand
Posts: 1056
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Here you get into DB-specific (and thus non-standard) SQL.
In MySQL, for instance, you would say
SELECT ... LIMIT 9, 10;
the first number is the number of the first row you want (MySQL counts from 0, not 1); the second is the number of rows to return.
 
Phil Chuang
Ranch Hand
Posts: 251
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
thanks, exactly what I needed
 
Ron Newman
Ranch Hand
Posts: 1056
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Just be aware that this SQL statement probably will not work if you move from MySQL to some other database server.
 
Phil Chuang
Ranch Hand
Posts: 251
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Yah, I've got my app modular enough that I can hop databases by switching classes, so no biggy.
I'll probably stick with mysql for awhile anyway, free is good!
(although I miss views)
 
maneesh subherwal
Ranch Hand
Posts: 42
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
how would this work in SQL Plus?
Thanks,
Maneesh
 
Phil Chuang
Ranch Hand
Posts: 251
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Correllatingly (is that a word?),
is it possible to use this in prepared statements?
ie
..."LIMIT ?,?" and specify 9,10 as parameters?
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34095
337
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You can do anything in a prepared statement that you can do in command line SQL for the database under consideration.
 
Phil Chuang
Ranch Hand
Posts: 251
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
just to clarify, can I even include keywords as parameters in prepared statements?
such as
"SELECT * from blah where blah = "blah" ? ?,?"
with the parameters being LIMIT,10,10 ?
Or what if I wanted LIMIT to be an optional thing, is there a way to do a true/false sort of thing where if there is no page_number, num_results specified to do away with the whole LIMIT clause altogether?
example in pseudo-SQL:
(no limit or order by specified)
"WHERE ...;"
(limit specified)
"WHERE ... LIMIT ?, ?;"
(order by specified)
"WHERE ... ORDER BY ?;"
(limit and order specified)
"WHERE ... LIMIT ?, ? ORDER BY ?;"
Can these all be done in one prepared statement or should I make multiples?
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34095
337
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I think I misinterpreted the question. The parameters can't be keywords. I meant that you could do "SELECT * from blah where blah = ? limit 10, 10"
 
Phil Chuang
Ranch Hand
Posts: 251
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Then is there any way to do a LIMIT that will return all rows?
I could just say 0,1000000000 but I'd rather not be arbitrary
[ September 15, 2003: Message edited by: Phil Chuang ]
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic