SELECT DISTINCT on one column, with multiple columns in mysql

Question

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

I want to list of categories that will not repeated for e.g ( travel , accessories etc) but not repeated that i have mentioned in above image — Samir Sheikh
– Samir Sheikh, Commented Jan 2, 2018 at 11:50
I want like ( travel, accessorise, gift & flowers , hotel ... etc ) all categories but not repeated duplicate — Samir Sheikh
– Samir Sheikh, Commented Jan 2, 2018 at 11:53

Gordon Linoff · Accepted Answer · 2018-01-02 11:47:30Z

7

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;

answered Jan 2, 2018 at 11:47

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

C. Geek Over a year ago

just change the table spelling coupon to coupan

Samir Sheikh Over a year ago

@Gordon Linoff , when i used 3rd column getting error.

C. Geek · Accepted Answer · 2018-01-02 12:25:29Z

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

edited Jan 2, 2018 at 12:25

answered Jan 2, 2018 at 11:57

C. Geek

3472 silver badges16 bronze badges

6 Comments

Samir Sheikh Over a year ago

i also want store image in list

C. Geek Over a year ago

@samirsheikh please check the modification

Samir Sheikh Over a year ago

given error "#1052 - Column 'category' in field list is ambiguous "

C. Geek Over a year ago

sorry, fixed it @samirsheikh

Samir Sheikh Over a year ago

@ C. Geek error: "#1054 - Unknown column 'q.store_image' in 'field list''

|

Rohan Varma · Accepted Answer · 2018-01-04 09:17:34Z

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

edited Jan 4, 2018 at 9:17

answered Jan 2, 2018 at 13:48

Rohan Varma

1017 bronze badges

Comments

Amit-Inex Patel · Accepted Answer · 2018-01-02 12:15:02Z

-3

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`

edited Jan 2, 2018 at 12:15

answered Jan 2, 2018 at 12:01

Amit-Inex Patel

4893 silver badges15 bronze badges

2 Comments

Samir Sheikh Over a year ago

Amit, I also want store_image column in list

Amit-Inex Patel Over a year ago

then add store_image in query. I just updated query. check it.

Collectives™ on Stack Overflow

SELECT DISTINCT on one column, with multiple columns in mysql

4 Answers 4

2 Comments

6 Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

6 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related