4

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

Samir Sheikh
  • 2,283
  • 2
  • 19
  • 37

4 Answers4

6

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;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
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
C. Geek
  • 347
  • 2
  • 16
0
SELECT category , MAX(`offer_id`) , store_image 
FROM  `tbl_coupan_offer` 
GROUP BY `category`

If multiple data coming then just add DISTINCT after SELECT

Rohan Varma
  • 101
  • 7
-2

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`
Amit-Inex Patel
  • 481
  • 3
  • 15