CODEWITHSUNDEEP
mysqlgreatest-n-per-group
Puttaporn Junlong
Puttaporn Junlong

Reputation: 23

How to find MAX Count value for each group separately

Here is my query

SELECT q2.item_code
     , q2.SELL_DELIVERY_AMOUNT
     , q2.count_amount
  FROM 
     ( SELECT i.item_code
            , s.SELL_DELIVERY_AMOUNT 
            , count(s.SELL_DELIVERY_AMOUNT) count_amount
         FROM sell_delivery_items s 
            , items i
        WHERE s.item_id = i.item_id
        GROUP 
           BY SELL_DELIVERY_AMOUNT
            , item_code
     ) q2
 order 
    by item_code
     , count_amount desc

and the result is

item_code   SELL_DELIVERY_AMOUNT     count_amount
0001        50                       2
0001        100                      1
0002        150                      3

I need to show only the highest count_amount row for each item_code group. I've done the search on some answer but still can't apply to mine.

This is the result i want

item_code   SELL_DELIVERY_AMOUNT     count_amount
0001        50                       2
0002        150                      3

How can i do this? thanks you for all answer.

Upvotes: 0

Views: 372

Answers (3)

Puttaporn Junlong
Puttaporn Junlong

Reputation: 23

I've got the solution. Here's mine:

SELECT q3.item_code, q3.item_name, substring_index(q3.AMOUNT_LIST, ',' , 1) as most_amount_count, 
substring_index(q3.count_list,',',1) as count_time from

(SELECT q2.item_code, q2.item_name, GROUP_CONCAT(SELL_DELIVERY_AMOUNT ORDER BY q2.count_amount DESC) as AMOUNT_LIST, 
GROUP_CONCAT(q2.count_amount ORDER BY q2.count_amount desc) as count_list from

    (SELECT  i.item_code, i.ITEM_NAME, s.SELL_DELIVERY_AMOUNT , count(s.SELL_DELIVERY_AMOUNT) as count_amount
    FROM sell_delivery_items s , items i
    WHERE s.item_id = i.item_id
    GROUP BY SELL_DELIVERY_AMOUNT, item_code
    order by item_code, count_amount desc
    )  as q2

group by q2.item_code )as q3

Upvotes: 1

gabriel garcia
gabriel garcia

Reputation: 367

What about this?

SELECT  q2.item_code, q2.SELL_DELIVERY_AMOUNT, max(q2.count_amount)
FROM 
(SELECT  i.item_code, s.SELL_DELIVERY_AMOUNT , count(s.SELL_DELIVERY_AMOUNT) as count_amount
FROM sell_delivery_items s , items i
WHERE s.item_id = i.item_id
GROUP BY SELL_DELIVERY_AMOUNT, item_code) as q2
group by q2.item_code
order by count_amount  desc

In here order by item_code, count_amount desc you are ordering item_code by natural order, a < z, so it's not needed as it's being ordered by the group by.

Upvotes: 0

tember
tember

Reputation: 1496

This is untested as I do not have MySQL - but something like this should work:

 SELECT  q2.item_code, q2.SELL_DELIVERY_AMOUNT, q2.count_amount
 FROM 
(SELECT  i.item_code, s.SELL_DELIVERY_AMOUNT , count(s.SELL_DELIVERY_AMOUNT)     as count_amount
FROM sell_delivery_items s , items i,  @rownum := @rownum + 1 AS rank
 WHERE s.item_id = i.item_id
 GROUP BY SELL_DELIVERY_AMOUNT, item_code) as q2
 where rank = 1
order by item_code, count_amount  desc

You need to add a "rank" or "row number" functionality to the inner query, then add a filter to the outer query to select only the top ranked row.

Here is another example of ranking in MySQL: Ranking by Group in MySQL

Upvotes: 0

Related Questions