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

asked 06 Dec '12, 09:53

MMacdonald's gravatar image

MMacdonald
1766617
accept rate: 0%

edited 15 Mar '13, 21:02

Mark%20Culp's gravatar image

Mark Culp
22.5k9129265


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';
permanent link

answered 06 Dec '12, 10:06

Volker%20Barth's gravatar image

Volker Barth
29.6k293444650
accept rate: 32%

edited 06 Dec '12, 12:05

Thanks. Where or how is this setting applied?

(06 Dec '12, 10:30) MMacdonald
Replies hidden

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.

(06 Dec '12, 10:39) Volker Barth

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

(06 Dec '12, 10:43) MMacdonald
Replies hidden

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

(06 Dec '12, 11:10) Reimer Pods

12.0.1.3457

(06 Dec '12, 11:13) MMacdonald

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';
(06 Dec '12, 11:38) Reimer Pods
Replies hidden

Thanks, this worked.

(06 Dec '12, 11:53) MMacdonald

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

(06 Dec '12, 12:02) Volker Barth
showing 4 of 8 show all flat view

I believe the equivalent SQL/Anywhere syntax is

SELECT TOP 1 * FROM users ORDER BY id ASC
permanent link

answered 07 Dec '12, 12:01

BudDurland's gravatar image

BudDurland
3169920
accept rate: 0%

1

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.

(10 Dec '12, 04:09) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×47
×15
×5

question asked: 06 Dec '12, 09:53

question was seen: 1,719 times

last updated: 15 Mar '13, 21:02