How to select all columns of a table with unique combination of just some of them in MySQL?-CodePudding

I have a MySQL table named "Products" with the following structure:

id | name   | variety      | t_min | t_max | entity
====================================================
1  | hake   | salmo salar  | -5.27 | 10.3  | entity1
2  | salmon | null         | -2.45 | 12.9  | entity1
3  | cod    | gadus morhua | -4.98 | 11.98 | entity1
4  | hake   | salmo salar  | -7.87 | 9.35  | entity1
5  | hake   | salmo salar  | -2.76 | 8.46  | entity1

The desired result I would want is:

id | name   | variety      | t_min | t_max | entity
====================================================
1  | hake   | salmo salar  | -5.27 | 10.3  | entity1
2  | salmon | null         | -2.45 | 12.9  | entity1
3  | cod    | gadus morhua | -4.98 | 11.98 | entity1

I want to select all the columns of the table but avoid repeating rows that have the same name variety in this case. Just using DISTINCT clause doesn't work because if I use it I just can select the columns I want to be unique, so how can I do it?

CodePudding user response：

Use window function

select *
from (
  select *, row_number() over(partition by name, variety order by id) rn
  from products
) r 
where r.rn = 1
order by r.id

CodePudding user response：

try

select * from ( select * , ROW_NUMBER() OVER(PARTITION BY name ORDER
BY id) AS row from Products ) as a where row = 1

hopefully it will give you the unique data.