[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
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
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
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;
SELECT category , MAX(`offer_id`) , store_image
FROM `tbl_coupan_offer`
GROUP BY `category`
If multiple data coming then just add DISTINCT after SELECT
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`