CODEWITHSUNDEEP
phpmysqlsql
Octavian
Octavian

Reputation: 4579

MySQL "group by" limit groups returned

If I apply a limit when selecting with group by, the limit is applied to the number of groups returned or the number of rows?

Myself, I want to limit the groups returned.

Here's my trial:

select * from orders group by customer_email limit 0,2

From this table:

id customer_email
0 [email protected]
1 [email protected]
2 [email protected]
3 [email protected]
4 [email protected]
5 [email protected]
6 [email protected]

I want returned rows 0,1,2,3,4,5.

Upvotes: 2

Views: 1639

Answers (1)

huysentruitw
huysentruitw

Reputation: 28131

Your question is a little ambiguous: the proposed query limits to 2 groups, and you seem to want all the rows with equal customer_email that are contained in the first 3 groups.

Anyway, here is my answer for as far as I correctly understand your question :)

SELECT *
FROM orders
INNER JOIN (
    SELECT customer_email
    FROM orders
    GROUP BY customer_email
    LIMIT 0,3
) AS temp
ON orders.customer_email = temp.customer_email

The sub query select customer_email from orders group by customer_email limit 0,3 selects the first 3 groups, then you select all the rows in order that contain the same customer_email as in the first 3 groups which would give you row 0, 1, 2, 3, 4, 5.

Upvotes: 1

Related Questions