SQL "LIMIT" synonym

[Anon]

Hello,

I'm developing a web application in PHP. I have Pervasive.SQL 2000 installed in Windows NT and it's working just fine. PHP's working fine too. I need to access several tables through ODBC. I ran some tests with small tables (around 7,500 records) and everything worked fine. But when trying to use larger tables (around 54,000 records) I got a problem which actually ended in 2 problems:

1. When runing a query, most of the time the result page is incomplete. My tables are still for tests, so nobody is acessing them but me. Lets say I get 120 records listed (the last one displays incomplete). I refresh the webpage and get the whole set of records listed correctly (around 200). I refresh again and get 132 (the last one incomplete again). Is this a server performance situation?

2. I thought it would be a good idea to limit the number of rows in my result to display them in several pages. I tried to use the "LIMIT" SQL statement inside my query, but didn't work. I found out that ODBC doesn't support the LIMIT statement. Someone suggested to use "SELECT TOP" instead and to add some code to make it work as I needed. ODBC supports SELECT TOP, but it didn't work either. I couldn't find TOP in the list of Pervasive.SQL reserved words, so I assume it's not supported by Pervasive. Is there a similar function or statement I can use for this that would work both for ODBC and Pervasive.SQL?

Thanks in advance for your response.

Tony Romo

Webmaster
Aceros Corey, S.A. de C.V.
Guadalajara, Jalisco, México

[matt89]

Query to select first 5 rows would look like this:

SELECT TOP 5 * FROM tableNameStr

[Ashley_Sheridan]

I just recently ran into a similar problem at work, where all the databases were MSSQL, and old versions at that, so the LIMIT statement was not available. What you need to do is use TOP creatively!

SELECT * FROM (SELECT TOP 10 * FROM (SELECT TOP 20 FROM table_name ORDER BY column_1) AS temp1 ORDER BY column_1 DESC) AS temp2 ORDER BY column_1

Basically, this returns the results 10 to 20, by first selecting the top 20 results, then reversing them and selecting the top 10 from those, then reversing them once more to get them in the correct order.

It's a pain to have to do it this way, but apparently the LIMIT command is available in newer version of MSSQL Server.

[NogDog]

FYI, folks, you've reopened a seven years old thread, so the odds that the OP will read your responses are probably pretty slim. :eek: 😉

[Ashley_Sheridan]

Sorry, didn't mean to troll, I just got browsing through the questions and what not, and stumbled across a problem I'd had myself. It might help I guess if someone else runs into it, as I remember some of the trouble I had looking for the solution to this one myself!

[NogDog]

Ashley Sheridan;10882582 wrote:

Sorry, didn't mean to troll, I just got browsing through the questions and what not, and stumbled across a problem I'd had myself. It might help I guess if someone else runs into it, as I remember some of the trouble I had looking for the solution to this one myself!

No biggie, and it's not like you dug it up, as matt89 opened it up week ago. Just wanted to point it out in case either of you thought you were doing good ol' Anon[ymous] a big favor. 😉