Is the LIMIT statement supported in a query?

[Former Member]

Is the LIMIT statement supported? I'm trying to use the FuelPHP ORM library. It's generating a query that works with MySQL but doesn't seem to with SQLAnywhere. I've traced it down to the query below.

Working MySQL example:

SELECT * FROM users ORDER BY id ASC LIMIT 1

SQLAnywhere example that doesn't work:

SELECT * FROM EM.Users ORDER BY UserId ASC LIMIT 1

Similar SQLAnywhere example that does work (by removing LIMIT):

SELECT * FROM EM.Users ORDER BY UserId ASC

[VolkerBarth · Accepted solution]

LIMIT is available with v12 but has to be "allowed" explicitly. From the docs:

The following statement allows the LIMIT keyword to be recognized as a keyword:

SET OPTION PUBLIC reserved_keywords = 'LIMIT';

---

EDIT: As Reimer has noted, the doc sample is missing a period, it should read

SET OPTION PUBLIC.reserved_keywords = 'LIMIT';

[Former Member · Accepted solution]

Thanks. Where or how is this setting applied?

[VolkerBarth · Accepted solution]

Use DBISQL (Interactive SQL) to connect as DBA to the database and run that statement. It is a public option, so it applies permantently to all further connections.

[Former Member · Accepted solution]

Thanks. I get "Syntax error near 'reserved_keywords' at line 1, column 19."

[reimer_pods · Accepted solution]

What SQLA version and build are you using (e.g. 12.0.1.3797)

[Former Member · Accepted solution]

12.0.1.3457

[reimer_pods · Accepted solution]

I had to double check twice until I saw the reason you've got an error: the period between PUBLIC and reserved_keywords was missing.

SET OPTION PUBLIC.reserved_keywords = 'LIMIT';

[Former Member · Accepted solution]

Thanks, this worked.

[VolkerBarth · Accepted solution]

Oops, I had just copied from the docs, and there's no period - I've made a DCX comment:)

[former_SQLA_member1694903]

I believe the equivalent SQL/Anywhere syntax is

SELECT TOP 1 * FROM users ORDER BY id ASC

[VolkerBarth]

Just to clarify:

The TOP / FIRST [START AT] clause is SQL Anywhere'c "classical" row limitation clause, available since v6 or v7, methinks. AFAIK it's not SQL standard, however supported by ASE and MS SQL Server, too.

v12 has added the LIMIT [OFFSET] clause, presumably for MySQL compatibility.