Hello,
i'm creating a web app that's running on an Advantage Database server, not my personal weapon of choice but that's what the company uses. I have a couple of big lists that the end-users need to be able to view however i can't seem to find a way to page through the results in SQL.
Is there something like LIMIT / OFFSET for Advantage Database? If no, any suggestions on approaching this?
thank you in advance!
According to this, the correct syntax for LIMIT in Advantage is SELECT TOP 10 * FROM YOURTABLE.
From my previous experience, Advantage Database is horrible and crap! We used it in a hospital environment for Patients Administration System (outsourced to 3rd party vendor), it was slow and unwieldy when multiple users access the system across the network and found that everything actually slows down. IT became a talking point as it caused unnecessary stress especially with the amount of people traffic in A&E in particular. Talk to the bosses about migrating over to {SQL Server|MySQL} or better and use a proper database, the SQL dialect on Advantage is not standard...and there is weird caching issues which causes latency...we ended up ditching Advantage for a better system and everyone was happy...
Hope this helps, Best regards, Tom.
I understand that LIMIT and a ROWNUM will be new features in an upcoming version of Advantage. http://feedback.advantagedatabase.com/forums/2671-general/suggestions/30213-return-query-specific-row-number-?ref=title
However, until then, I have used this in the past to select row 50-60.
select top 10 * from mytable where rowid not in (select top 50 rowid from mytable)
@tommieb75, you indicated that the SQL dialect was not standard. I have found that it is based on the standards containing most of the SQL-92 standard and some of the SQL-2003 features.