Is the LIMIT statement supported in a query?

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

select limits mysql

asked 06 Dec '12, 09:53

MMacdonald
176●6●6●17
accept rate: 0%

edited 15 Mar '13, 21:02

Mark Culp
24.9k●10●141●297

flat view

2 Answers:

active answers oldest answers newest answers popular answers

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 Barth
40.2k●361●549●822
accept rate: 34%

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
316●9●9●20
accept rate: 0%

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

flat view

Your answer

toggle preview

community wiki:

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

learn more about Markdown

Question tags:

select ×69
mysql ×16
limits ×7

question asked: 06 Dec '12, 09:53

question was seen: 5,652 times

last updated: 15 Mar '13, 21:02

SAP SQL Anywhere

Is the LIMIT statement supported in a query?

Follow this question

Related questions