Selecting a Range of Rows

[ghaleon]

I was curious if you might know how I would do a range of rows in PSQL starting at row 10 with a limit of 20 rows.

In MYSQL it would just be select * from table limit 10, 20

Thanks in advance.

 

[TomKane]

Hi,

I wonder if these are of any help to you:

SELECT TOP 10 * FROM person
-- returns 10 rows
SET ROWCOUNT = 5;
SELECT TOP 10 * FROM person;
-- returns 5 rows
SET ROWCOUNT = 12;
SELECT TOP 10 * FROM person ORDER BY id;
-- returns the first 10 rows of the full list ordered by column id.

Regards,
Tom

 

[ghaleon]

From my understanding ROWCOUNT is almost the same as TOP.
ROWCOUNT returns the first # of values.. and so does TOP.

I need something that makes the query START at a certain row # and then use TOP to pick the next set of numbers.

 

[ghaleon]

Maybe there is some global variable/function that returns the current row??

 

[mirtheil]

I'm not aware of any function or feature that would automatically do what you are trying to do.

You might have to restructure your query to include some unique value that you can restrict on (for example a product id that's indexed).

What does your data look like?

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[ghaleon]

no record numbers =)

it was all created on an old version of btrieve and i guess the original designers didnt think to put it in :/

 

[mirtheil]

Anything unique about the record?

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[ghaleon]

Not really. Heres an example of the data and what I am trying to do.

http://quicksilverbbs.com/itemlist.php?t=1

Basically it lists all of the weapons in the dat file. It displays 20 items per page.

This gives a pretty big load time for my larger dat files and instead of selecting ALL items i would like to select only the ones needed for each page.

The data displayed is BASICALLY the data I can use... So there is no other choice but to find a way to get the rownumbers.

 

[ghaleon]

Here is one way IT COULD be done:
select * from table where ROWNUMBER < $page*20 AND ROWNUMBER > $page-1*20

That is of course ROWNUMBER was a variable

 

[mirtheil]

ROWNUMBER isn't defined in PSQL.

Actually, if "Name" is unique, you could use that. Something like:
select top 20 * from table order by name
then get the that 20th name and use it in the next query:
select top 20 * from table where name > '20th name' order by name

Are the displayed fields the only ones that are defined in the DDFs? Can you post the CREATE TABLE / CREATE INDEX statements for the table?

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[ghaleon]

That works Kinda a different way of doing it but it works non-the-less. Thx for your help!

 

[ghaleon]

After putting this into play I'm still going to run into issues.

Basically now I will need to run 3 queries instead of 1
One to find the top number
One to find the 20 records in the page data
One to find the total values in the table

Also when I am running the first query to find the top number, when I get into the larger page numbers I'm basically looking doing a query on all those numbers anyways..

For Example
$page = 40
select top ($page-1)*20 Name from table
will select the top 780 records

So unfortunately it WORKS but there is still going to be an issue here..
The old way I have it works.. But unfortunately I do tend to get unwanted server lag when people initially load the page :/

 

[TomKane]

Hi,

Have you thought about trying a cursor? I know it's frowned upon and tends to cause great debate - but it could be worth considering.

Regards,
Tom

 

[ghaleon]

I would rather not if i can avoid it... Think there are any plans to change PSQLs limit function into one more similar to MySQLs in newer versions of Pervasive?

http://php.about.com/od/mysqlcommands/g/Limit_sql.htm

Personally I think that would be awesome...

For now I guess my best option is to just go with what I have at the moment.. at least until a row variable or limit function is created :/

 

[mirtheil]

I don't know if Pervasive is planning to add it. Do you know if it works in MS SQL? That seems to be what PSQL is compared to in terms of functionality.
I would also suggest contacting Pervasive directly and telling them it would be be a good enhancement. Be prepared to give a business case for it.

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[ghaleon]

My Comment:
Would be nice to have a variable in PSQL that returns the current row number or a function such as mysql's 'limit' function that selects a range of rows. ie select * from table limit 10, 20 ... basically that would select 20 records AFTER the first 10 OR select * from table where ROWNUMBER > 10 AND ROWNUMBER < 30 ... this would do the same thing. Personally I think this is a very important feature to people with high content tables that Pervasive currently does not have (that I can find)



Response from Pervasive:

Dear #######,

Thank you for your request for support today.

In order to provide complimentary support, we will need the Pervasive part number and serial located on the software box, license agreement, or registration card. Then will need for you to provide a copy of an invoice dated within the last 60 days. Please fax it to 512.231.6299 or e-mail it to customersupport@pervasive.com.

Once we receive this information, we will process your request immediately.



Regards,

Gigi Hutchison
CIC Representative
EMEA Toll Free: 00800.1212.3434
NA Toll free: 800.287.4383
EMEA: +32.70.233.761
Americas: +1.512.231.6000

http://www.pervasive.com

My Response:
I'm just sending this as a suggestion for your next upgrade. Your SQL is lacking and I'm just here giving you ideas to improve it.

 

[ghaleon]

I tried to call them and ask if there was such a function but they wanted $250.00 or something like that... I'd rather deal with a little lag.

 

[mirtheil]

I wouldn't send it to support. I would talk to your sales rep or the product manager.

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[mirtheil]

One more thing, I just tried it in MS SQL. It's not available there either.

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[ghaleon]

Nope this only works (that I know of in MySQL 4+).
There are a couple work arounds in MS SQL tho.

select top * from ( select top * from table order by column ) order by column

and

select * from
(
select top * from
(
select top (+) from table order by column ASC
) T1 order by column DESC
) T2 order by column ASC

Both of which throw errors in PSQL