CODEWITHSUNDEEP
mysql
priya
priya

Reputation: 1

How to fetch rows where count in sub query is max for multiple ids

I have a table as below

s_no         medicine_id     store_id
1                85            1
2                10            1
3                51            2
4                85            2
5                85            1
6                85            3
7                85            1
8                51            2
9                10            3
10               10            3
11               10            3

I want to fetch max count of medicine_id for each store.I have used below select query and could find half of the result.

SELECT store_id, MAX( mycount ) , medicine_id
FROM (
    SELECT medicine_id, store_id, COUNT( medicine_id ) AS mycount
    FROM medicines
    GROUP BY medicine_id, store_id
) AS t
GROUP BY medicine_id, store_id

From this query i get result as below

medicine_id      store_id            mycount
85                1                   3
85                2                   1
85                3                   1
51                2                   2
10                1                   1
10                3                   3

What result i want is fetch rows where mycount is max for each medicine_id

medicine_id      store_id            mycount
85                1                   3
51                2                   2
10                3                   3

Upvotes: 0

Views: 64

Answers (3)

helvete
helvete

Reputation: 2673

There is another solution, in a way similar to Slava Rozhnev's.

Just do a LEFT JOIN to the same selection WHERE the count is higher and then take the records where the "higher" value is null

SELECT first.*
FROM (
    SELECT medicine_id, store_id, COUNT(medicine_id) AS mycount
    FROM medicines
    GROUP BY medicine_id, store_id
) first
LEFT JOIN (
    SELECT medicine_id, store_id, COUNT(medicine_id) AS mycount
    FROM medicines
    GROUP BY medicine_id, store_id
) second ON first.medicine_id = second.medicine_id AND first.mycount < second.mycount
WHERE second.mycount IS NULL

Returns:

+-------------+----------+---------+
| medicine_id | store_id | mycount |
+-------------+----------+---------+
|          10 |        3 |       3 |
|          51 |        2 |       2 |
|          85 |        1 |       3 |
+-------------+----------+---------+
3 rows in set (0.00 sec)

Upvotes: 1

Slava Rozhnev
Slava Rozhnev

Reputation: 10163

The next bit a complicate query should to solve this problem:

SELECT tbl0.*
FROM (
SELECT medicine_id, store_id, COUNT(medicine_id) AS mycount
  FROM medicines
  GROUP BY medicine_id, store_id
) tbl0
-- join give stores with max count of medicine_id
JOIN (
  -- get max count for each medicine_id
  SELECT medicine_id, MAX(mycount) maxcount
  FROM (
    -- get medicine_id count for each store_id
    SELECT medicine_id, store_id, COUNT(medicine_id) AS mycount
    FROM medicines
    GROUP BY medicine_id, store_id
  ) tbl1 group BY medicine_id
) tbl2 ON tbl0.medicine_id = tbl2.medicine_id AND tbl0.mycount = tbl2.maxcount;

Here you can try it SQLize.online

This query can be used for MySQL 5.* and MySQL 8.* as well. Since MySQL 8.0 window function can be used for reduce query complexity

Upvotes: 0

forpas
forpas

Reputation: 164099

If your version of MySql is 8.0+ then you can do it with FIRST_VALUE() and MAX() window functions:

SELECT DISTINCT 
  medicine_id,
  FIRST_VALUE(store_id) OVER (PARTITION BY medicine_id ORDER BY COUNT(*) DESC) store_id, 
  MAX(COUNT(*)) OVER (PARTITION BY medicine_id ORDER BY COUNT(*) DESC) AS mycount
FROM medicines
GROUP BY medicine_id, store_id

See the demo.
Results:

> medicine_id | store_id | mycount
> ----------: | -------: | ------:
>          10 |        3 |       3
>          51 |        2 |       2
>          85 |        1 |       3

Upvotes: 0

Related Questions