List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:January 2 2001 5:40pm
Subject:Re: LIMIT first offset 0 or 1 ? and other LIMIT problems
View as plain text  
At 9:29 AM -0800 1/2/01, Angel Behar wrote:
>Hi, I'm running mysql 3.23.16-alpha-log w/o a problem, but now I'm confused
>about the LIMIT feature.  Must I use 0 as the first offset or use 1, the
>following query ruturns five rows that match the criteria :

Record offsets for LIMIT begin with 0, not 1, as documented in the MySQL
manual.

>
>mysql> SELECT codigo from muebles where cve_dpto='1' AND cve_clase = '10'
>AND cve_familia='02' AND agotado='D' LIMIT 1,10;
>+--------+
>| codigo |
>+--------+
>| 181129 |
>| 181145 |
>| 181161 |
>| 181201 |
>| 181463 |
>+--------+
>5 rows in set (0.02 sec)
>
>The exactly same query , but with offset 0 or without an offset returns 6
>rows :

This makes sense, because by specifying an offset of 1 in the previous query,
you're telling it to skip the first row.  You can think of the first number
either as the number of rows to skip, or as the 0-based index of the first
row you want to see.

>
>mysql> SELECT codigo from muebles where cve_dpto='1' AND cve_clase = '10'
>AND cve_familia='02' AND agotado='D' LIMIT 0,10;
>+--------+
>| codigo |
>+--------+
>| 181031 |
>| 181129 |
>| 181145 |
>| 181161 |
>| 181201 |
>| 181463 |
>+--------+
>6 rows in set (0.02 sec)
>
>So, I have the same problem when only match 1, 2 or 3 rows and for example
>if I run another query wich matched 398 rows, the limit clause only works
>with the first 15 offsets, lets say :
>SELECT * from ropa where cve_depto='3' AND cve_clase ='03' AND
>cve_familia='001' GROUP BY codigo LIMIT 160,10 returns 0 rows.

That's what will happen unless your query selects at least 161 rows.

By the way, perhaps you mean ORDER BY rather than GROUP BY?  I find that
with a GROUP BY col_name, a query returns only the first row from each group
of col_name values.  That in itself may cause your query to return fewer rows
than you're expecting.

>
>If any one can give an advice, I'll appreciate.
>
>Thanks in advance.


-- 
Paul DuBois, paul@stripped
Thread
LIMIT first offset 0 or 1 ? and other LIMIT problemsAngel Behar2 Jan
  • Re: LIMIT first offset 0 or 1 ? and other LIMIT problemsGerald L. Clark2 Jan
    • Re: LIMIT first offset 0 or 1 ? and other LIMIT problemsAngel Behar Rodriguez2 Jan
      • Re: LIMIT first offset 0 or 1 ? and other LIMIT problemsGerald L. Clark2 Jan
  • Re: LIMIT first offset 0 or 1 ? and other LIMIT problemsPaul DuBois2 Jan
Re: LIMIT first offset 0 or 1 ? and other LIMIT problemsMr.Y.SHIVAKANT3 Jan