1

I have below customer table with max qty.

I need a unique user who has max qty order by its group id.

Input: 

id, customer_id, customer_group_id   qty
1      1              3               1
2      1              3               10
3      1              3               5
4      2              2               10
5      2              2               1  
6      2              2               2 
7      3              1               5 
8      3              1               10
9      4              4               1
10     4              4               2
11     4              4               2

Output should be: 

id, customer_id, customer_group_id, qty
11     4             4               2
10     4             4               2 - This should be not selected
2      1             3               10
4      2             2               10
8      3             1               10

Query:

SELECT * FROM customer 
WHERE qty IN ( SELECT MAX(qty) FROM customer GROUP BY customer_id) 
ORDER BY customer_group_id DESC;

I tried above query but seems not working.

Jackson
  • 1,426
  • 3
  • 27
  • 60
  • Please check you mysql version there may be issue https://stackoverflow.com/questions/37951742/1055-expression-of-select-list-is-not-in-group-by-clause-and-contains-nonaggr – Shibon May 24 '17 at 04:24
  • Nope @Shibon that is not the case. I have updated question. Please check – Jackson May 24 '17 at 10:08

0 Answers0