How to select unique values?

Question

I am trying to get this query to only display 1 result (not LIMIT) in this example:

SELECT company_id  FROM `location` WHERE `state` = 'XX'

It results are similar to this:

16 9 9 9 9 30 30 30 45 50 50 50 50 57 7 79 80 80 80 80 120 120 120 120 120 128 131 120 265 265

I am aiming for these results:

16 9 30 45 50 57 7 79 80 120 128 131 120 265

Answer 1

Use DISTINCT keyword

SELECT DISTINCT company_id  FROM `location` WHERE `state` = 'XX'

It will give the desired result.

Answer 2

Simply use DISTINCT when you want to retrieve distinct result set.....

SELECT DISTINCT company_id  FROM `location` WHERE `state` = 'XX'

You can use Group By as an alternative to DISTINCT in following way:

 SELECT company_id  FROM `location` WHERE `state` = 'XX' GROUP BY company_id  

The DISTINCT and GROUP BY generates same execution plan as performance wise though I strongly recommend you to use DISTINCT in your scenario......

Answer 3

Just add DISTINCT keyword before you column name

SELECT DISTINCT company_id FROM `location` WHERE `state` = 'XX'

The DISTINCT keyword in a SELECT statement removes duplicates returned by a query.