CODEWITHSUNDEEP
mysqlsql
XCS
XCS

Reputation: 28147

SQL limit number of groups

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?

Upvotes: 5

Views: 2072

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

Your query does not return the first visit by date. To do this, you need to actually join in this information:

select v.*
from visits v join
     (select clientid, MIN(date) as mindate
      from visits
      group by clientid
     ) vd
     on v.clientid = vd.clientid and v.date = vd.date
order by clientid
limit $from - 1, $to - $from

Your originally query is returning an arbitrary set of columns about a given client. These columns are not even guaranteed to be from the same record. This is because you are using a MySQL (mis)feature, where you can have columns in the select clause that are not in the group by clause and are not the arguments to an aggregation function.

Upvotes: 1

hjpotter92
hjpotter92

Reputation: 80639

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.

Upvotes: 6

Colleen
Colleen

Reputation: 25489

With the dbms and syntax you're using, the second number in the limit expression is the number of results allowed, not offset endpoints. So you're starting at 6 and allowing the next 12 results, not getting results 6-12.

If you want to get results 6-12, use limit 5,7

Upvotes: 2

Kara
Kara

Reputation: 6226

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)

Upvotes: 2

Related Questions