Selecting all rows in a table which distinct key-CodePudding

I want to only retrieve the table rows where productId is DISTINCT

So for this case, I want to retrieve:

 productID| name | price | ...
     1    |  ... |  ...  | ...
     2    |  ... |  ...  | ...
     3    |  ... |  ...  | ...

filtering out the repeated productID

What I have tried - Product is the table:

SELECT DISTINCT `productId`  FROM `Product`

SELECT * 
FROM `Product` 
WHERE DISTINCT `productId`

Both of these don't work, any help would be greatly appreciated.

CodePudding user response：

You can join on a subquery which returns ids with their minimum product index, the subquery uses group by to get productIds as a "distinct" value and filter with having and the aggregate function MIN and get the record with the minimum productindex (ie: the first record with that productId)

SELECT p.* 
FROM Product as `p`
inner join
(
   select `productId`, MIN(`productindex`) as `productindex`
   from Product
   group by `productId`
   having MIN(`productindex`)
) as `x`
on `x`.`productId` = `p`.`productId` and `x`.`productindex` = `p`.`productindex`