Mysql – Selecting distinct values by column value

MySQL

I have the following table:

+-----------------------------+-----------+
| keyword                     | data_type |
+-----------------------------+-----------+
| red                         | ctr       |
| blue                        | ctr       |
| purple                      | ctr       |
| orange                      | ctr       |
| red                         | var       |
| blue                        | var       |
| black                       | var       |
| yellow                      | var       |
+-----------------------------+-----------+

I'd like to be able to select only those which are distinct by data_type.

For example:
If I checked by data_type: var, 'black' and 'yellow' would be returned.

If I checked by data_type: ctr, 'purple' and 'orange' would be returned.

I'd normally provide an example query of what I've done, thus far, but I don't know where to start with this one – help is much appreciated.

Best Answer

Pretty straight forward:

SELECT * FROM table
WHERE data_type = 'var'
AND keyword NOT IN (SELECT keyword FROM table WHERE data_type <> 'var');
Related Question