[Solved] SELECT DISTINCT on one column, with multiple columns in mysql

I have to display result with select multiple column but distinct on only one column i have also used following queries and also refereed below link.

Query One.

select category , offer_id, store_image from  `tbl_coupan_offer` where
`offer_id` in (Select max(`offer_id`) FROM  `tbl_coupan_offer` group by `category`)  

Above queries return all record with including duplicate

I want only display distinct category not repeated

Following is image

enter image description here

On image you can see Travel and Accessorises repeated

Second query

SELECT DISTINCT `category`,`offer_id`,`store_image` FROM `tbl_coupan_offer` 

I also refereed this link Select all columns from rows distinct on one column

Solution #1:

Like Gordon said but as a subquery to get the appropriate image.

SELECT DISTINCT q.category, ch_offer_id, store_img
FROM
(
    SELECT category, MAX(offer_id) ch_offer_id
    FROM tbl_coupan_offer
    GROUP BY category
) q
JOIN tbl_coupan_offer
ON q.ch_offer_id=tbl_coupan_offer.offer_id
Respondent: Samir Sheikh
Solution #2:

Remove offer_id from the select:

SELECT DISTINCT category
FROM tbl_coupon_offer;

If you want one offer_id, then use GROUP BY:

SELECT category, MAX(offer_id)
FROM tbl_coupon_offer
GROUP BY category;
Respondent: C. Geek
Solution #3:
SELECT category , MAX(`offer_id`) , store_image 
FROM  `tbl_coupan_offer` 
GROUP BY `category`  

If multiple data coming then just add DISTINCT after SELECT

Respondent: Gordon Linoff
Solution #4:

Check your database engine type. It is “InnoDB” or not. If your database and table both existed and if you find this error #1146 Table xxx doesn’t exist. Then read more about database engine.

If all is right, then query of Gordon is right.

SELECT `category`, `offer_id`, `store_image`
FROM `tbl_coupon_offer`
GROUP BY `category`
Respondent: Rohan Varma
The answers/resolutions are collected from stackoverflow, are licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0 .

Leave a Reply

Your email address will not be published.