Menu
I am trying to create a simple pagination but it seems to fail to limit the number of results.
SELECT * FROM visits GROUP by clientID ORDER BY 'date' LIMIT $from, $to
I want to get only the first visit (chronologically) of every client and paginate the results.
If I make this query with $from = 6, $to = 12 it returns like 8 rows instead of 7.
What I'm doing wrong?
376
Using GROUP BY with LIMIT There's one thing to be aware of as you group by multiple columns: SQL evaluates the aggregations before the LIMIT clause. If you don't group by any columns, you'll get a 1-row result—no problem there.
You can easily change this limit by going to MySQL Workbench >> Edit >> Preferences >> SQL Queries tab. Over here you will option to Limit Rows. You can set this to very high value or uncheck the option. When you uncheck that option, it will retrieve all the rows from a query (equivalent to no limits).
The LIMIT clause is used to set an upper limit on the number of tuples returned by SQL. It is important to note that this clause is not supported by all SQL versions. The LIMIT clause can also be specified using the SQL 2008 OFFSET/FETCH FIRST clauses. The limit/offset expressions must be a non-negative integer.
The LIMIT clause is used to specify the number of records to return. The LIMIT clause is useful on large tables with thousands of records. Returning a large number of records can impact performance.
From MySQL's docs on LIMIT clause
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).
When you chose $from as 6 and $to as 12; you are not selecting from 6 to 12; you will be selecting 12 rows, starting from $from + 1 = 7.
188
The first argument to the LIMIT cluase is the starting offset and the second is the number of rows to return. Therefore your query should be:
... LIMIT $from, ($to - $from)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
