I recently stumbled upon example codes, which differed by these notations.

SELECT * FROM table LIMIT 0, 1
SELECT * FROM table LIMIT 1

The first argument should be considered as the offset if I'm not wrong, the purpose of those two queries is to limit the selection to up the first row of table

Is there any negative effect when leaving out the offset/how is it possible to leave it out in the first place? Or did I misunderstood the queries?

  • 2
    No, you got it right, they are equivalent. This is also: LIMIT 1 OFFSET 0. But you should really include ORDER BY in any query that has LIMIT, unless there is a specific reason not to. – ypercubeᵀᴹ Jul 3 '15 at 9:06
  • Missed the offset keyword, thanks! – Gimu Jul 4 '15 at 7:15
up vote 9 down vote accepted

As the documentation says:

The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. LIMIT takes one or two numeric arguments, which must both be nonnegative integer constants (except when using prepared statements).

With two arguments, the first argument specifies the offset of the first row to return, and the second specifies the maximum number of rows to return. The offset of the initial row is 0 (not 1).

The LIMIT clause is used in the SELECT statement to constrain the number of rows in a result set. The LIMIT clause accepts one or two arguments. The values of both arguments must be zero or positive integer constants.

The following illustrates the LIMIT clause syntax with 2 arguments:

SELECT * FROM tbl
LIMIT offset, count;

Let’s see what the offset and count mean in the LIMIT clause:

  • The offset specifies the offset of the first row to return. The offset of the first row is 0, not 1.
  • The count specifies maximum number of rows to return.

When you use LIMIT with one argument, this argument will be used to specifies the maximum number of rows to return from the beginning of the result set.

SELECT * FROM tbl LIMIT count;

The query above is equivalent to the following query with the LIMIT clause that accepts two arguments:

SELECT * FROM tbl LIMIT 0, count;

The LIMIT clause often used with ORDER BY clause. First, you use the ORDER BY clause to sort the result set based on a certain criteria, and then you use LIMIT clause to find lowest or highest values.

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.