One correction, I forgot to alias the table in the third example (That's
what I get for typing straight into the body.
Should be:
SELECT o.Column1, o.Column2, o.Column3 FROM Sometable o
WHERE (SELECT Count(*) FROM Sometable i
WHERE i.Column1 < o.Column1)
BETWEEN 31 AND 40
Still ugly however you look at that one. :-(
Steve H.
-----Original Message-----
From: Steve Howard [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, October 02, 2001 7:08 PM
To: Purcell, Scott; [EMAIL PROTECTED]
Subject: RE: Select X number of rows
Three suggestions depending on the DBMS you are using:
1. This method is supported by MS SQL 7.0 or later:
SELECT TOP 20 Column1, Column2, Column3 FROM Sometable
WHERE Column1 NOT IN
(SELECT TOP 40 Column1 FROM Sometable
ORDER BY Column1)
ORDER BY COLUMN1
That will give you rows 41-60.
2. Using MySQL or PostGreSQL:
SELECT Column1, Column2, Column3 FROM Sometable
ORDER BY Column1 LIMIT (20, 20)
Gets results from rows 21-40.
3. This next will work from most DBMS's, but this is a relative dog
performance wise. If you have a DBMS specific way of paging through the
results, I recommend it over this, but if there is no other way, then do
this:
SELECT o.Column1, o.Column2, o.Column3 FROM Sometable
WHERE (SELECT Count(*) FROM Sometable i
WHERE i.Column1 < o.Column1)
BETWEEN 31 AND 40
That will get you result rows number 31-40 inclusively (10 rows).
In any of those methods, it is imperative that you have a primary key for
them to work.
Other DBMS's may have different methods for doing this. See your docs for
that.
To page through the results, send a hidden field with your page to let you
know where your start number is, then just issue the query the next time
with the numbers so that you can get the next page of results.
Does this help?
Steve H.
-----Original Message-----
From: Purcell, Scott [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, October 02, 2001 7:36 AM
To: '[EMAIL PROTECTED]'
Subject: Select X number of rows
Hello,
I am a Perl guy, not a DBA. Anyway, if I have a couple of DBs with X amount
of records, can I select from three databases, acquire the first 20 records
(sort by ASC), then (show them on the web) and when they hit next, show the
next 20, eg. 21-40 and so on and so on. Also, If that is doable, how do I
know how many pages. Is there some command in SQL that would tell me how
many records are in the three DBS without me having to acquire and count all
the records first?
I hope this is not too much of a SQL question, but as I am building this in
Perl. I hope I do not offend anyone with this morning off-perl question.
Thanks you very much,
Scott Purcell
