CODEWITHSUNDEEP
mysqlsql
Tinagaran
Tinagaran

Reputation: 39

Get DISTINCT column based on another DISTINCT column in the same row

I have 4 columns in a table name products

id|p_name| p_img       |
1 | Xs   | xsmax.png   | 
2 | Xs   | xr.png      | 
3 | XR   | xs.png      |

I want to get DISTINCT value of p_name and echo its p_img. So I used the code below.

But, it displays the all the p_image because there are no duplicates. I want to display based on the p_name.

SELECT DISTINCT product_name, product_img FROM products
while($row=mysqli_fetch_assoc($result)){

}

expected output
p_name| p_img       |
Xs    | xsmax.png   |  
XR    | xs.png      |

Upvotes: 1

Views: 62

Answers (1)

Barbaros Özhan
Barbaros Özhan

Reputation: 65288

Seems you need an aggregation in the subquery use :

SELECT product_name, product_img 
  FROM products
 WHERE (id, product_name) in
       ( SELECT MAX(id), product_name
           FROM products
          GROUP BY product_name   
       )

depending on "so I want to show only one with the first image that is in the entry and ignore the rest."

Upvotes: 1

Related Questions