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

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

CodePudding user response：

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;

CodePudding user response：

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
);