How to apply distinct on a column which has similar values

Question

I have a table with column id and text as below:

id  text

001   hello
002   hello
003   hi
004   hello
005   hi
006   test

I need to show list of suggestion for given id, say '001'

Now its going to fetch all the possible records. Even if I apply DISTINCT here I doubt it will still show all values as their ids are unique.

Is it possible to select one value only for 'hello'? If yes, which Id will it show? I think its not a good idea to select this way or is it a common case?

What I'm expecting is, the suggestion list should be as below:

id  text
001 hello
003 hi
006 test

Unfortunately, I couldn't use GROUP BY here as I'm using LIKE in the query.

SELECT id, text FROM `tablename` AS `table` WHERE `id` LIKE '00'

Please advise.

yes, i did, it still shows all the same rows for text – 112233 Apr 21 '17 at 04:15 — 112233, Apr 21 '17 at 04:15
or just write `GROUP BY text` it will work – webpic Apr 21 '17 at 04:33 — webpic, Apr 21 '17 at 04:33

score -1 · Answer 1 · answered Apr 21 '17 at 06:00

-1

referred here: https://dev.mysql.com/doc/refman/5.7/en/exists-and-not-exists-subqueries.html

This solution works for my case:

SELECT DISTINCT text FROM tablename
  WHERE EXISTS (SELECT * FROM tablename
                WHERE `id` LIKE '%00%')

answered Apr 21 '17 at 06:00

112233

2,406
3
38
88

How to apply distinct on a column which has similar values

1 Answers1