MYSQL limit values ​​for a specific column value

Question

Pulling some coupons from the database. Each coupon has a merchantid column that contains an identifier for the seller for whom the coupon also belongs.

I am trying to build a query that pulls out 5 coupons, but I want only 1 coupon per merchantid . I do not need several coupons with the same merchantid .

Answer 1

you can use

 SELECT * FROM coupons GROUP BY merchantid LIMIT 0,5; 

And it will work because

MySQL expands the use of GROUP BY so that the selection list can refer to non-aggregated columns not named in the GROUP BY clause ( see docs )

If you do not want MySQL to decide which merchantid to save, you can add your condition (in the example below - save the seller with the most clicks) using a subquery:

FIXED:

 SELECT c1.* FROM coupons c1 JOIN ( SELECT t.merchantid, MAX(t.numberofclicks) maxnumberofclicks FROM coupons t GROUP BY t.merchantid ) c2 ON c1.merchantid = c2.merchantid AND c1.numberofclicks = c2.maxnumberofclicks LIMIT 0,5; 

And another (more concise and probably faster on large datasets) way to trim the cat:

 SELECT c1.* FROM coupons c1 JOIN coupons c2 ON c1.merchantid = c2.merchantid GROUP BY c1.merchantid, c1.numberofclicks HAVING c1.numberofclicks = MAX(c2.numberofclicks) LIMIT 0,5; 

If you need 5 coupons with a maximum number of clicks, add ORDER BY c1.numberofclicks DESC to LIMIT 0,5 .

Answer 2

to try

 SELECT * FROM your_table_name GROUP BY merchantid LIMIT 0,5; 

this will give 5 lines that have different trading, but you can get the same result for different executions. if you want to randomize it, randomize 'A' inside 'LIMIT A,5' .