Question 22026 in Mysql Question posted in
The official MySQL documentation can be found here.

mysql : group by column (show column with higer value)

i have some rows on "MENU" table

id, shop, size
1,  1   , 3
2,  1   , 8
3,  2   , 5

i need to show 1 row for each shop so if shop id is same then show the row with high value on column size
and results should be like this

id, shop, size
2,  1   , 8
3,  2   , 5

also if size is 0 on both rows it will display just 1 row

i need something like this

SELECT * FROM menu GROUP by shop

but to show the row with high value

i have tried this but if rows have 0 on column size then it shows both of them

SELECT a.* FROM menu a                   
LEFT JOIN menu b            
ON a.shop=b.shop AND a.size< b.size
WHERE b.size NULL
Tags:

2

Answers


  1. 0

    Provided you’re using a recent version of MySql that supports window functions this is typically resolved with row_number:

    with t as (
  Select *, row_number() over(partition by Shop order by Size desc) rn
  from Menu
)
select Id, Shop, size
from t
where rn = 1;
    Login or Signup to reply.
  2. 0

    The most typical way to solve such tasks is with ROW_NUMBER, RANK or DENSE_RANK as shown in Stu’s answer.

    Another option: Get the maximum size per shop, then use this result to retrieve the rows:

    SELECT * 
FROM menu
WHERE (shop, size) IN
(
  SELECT shop, MAX(size)
  FROM menu
  GROUP by shop
);

    Another option: select rows for which no row with the same shop but a higher size exists:

    SELECT * 
FROM menu
WHERE NOT EXISTS
(
  SELECT null
  FROM menu better
  WHERE better.shop = menu.shop
  AND better.size > menu.size
);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search