MySQL Unique Values – How to Get Unique Values in Column That Can Have a Value or 0

MySQL

Mysql

on a simple table (all integers)
A B C

I want to query all rows where C is a distinct number or 0
for example:


0 1 4
4 5 6
6 5 0
5 6 0
7 5 4
8 5 6
9 5 0

Should give:

0 1 4
4 5 6
6 5 0
5 6 0
9 5 0

rows:

7 5 4
8 5 6

didnt match because in col C 4 and 6 already appear.
All the C=0 columns should appear.

Whats the best way to do a query that does distinct C or C=0 ?
the results order doesn't matter.

Best Answer

Try this:

SELECT * FROM mytable WHERE C=0 
UNION ALL
SELECT * FROM mytable WHERE C<>0 GROUP BY C;

The caveat here is that you have no control on which row will be returned from each set of rows that have the same values of "C"

mysql> desc mytable;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| a     | int(11) | YES  |     | NULL    |       |
| b     | int(11) | YES  |     | NULL    |       |
| c     | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql> select * from mytable;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|    0 |    1 |    4 |
|    4 |    5 |    6 |
|    6 |    5 |    0 |
|    5 |    6 |    0 |
|    7 |    5 |    4 |
|    8 |    5 |    6 |
|    9 |    5 |    0 |
+------+------+------+
7 rows in set (0.00 sec)

mysql> SELECT * FROM mytable WHERE C=0 
    -> UNION ALL
    -> SELECT * FROM mytable WHERE C<>0 GROUP BY C;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|    6 |    5 |    0 |
|    5 |    6 |    0 |
|    9 |    5 |    0 |
|    0 |    1 |    4 |
|    4 |    5 |    6 |
+------+------+------+
5 rows in set (0.00 sec)
Related Question