1

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.

7
  • the results order doesn't matter - Odd
    – Philᵀᴹ
    Commented Feb 13, 2017 at 15:05
  • What determines the order of the rows? Commented Feb 13, 2017 at 16:26
  • Is it significant that 014 was chosen instead of 754? Would it be ok to return 754 and drop 014? Commented Feb 13, 2017 at 18:21
  • @MichaelGreen yes its ok to return 754 and drop 014
    – Nir
    Commented Feb 13, 2017 at 19:04
  • @DuduMarkovitz there is a simple key A B C
    – Nir
    Commented Feb 13, 2017 at 19:05

2 Answers 2

1

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)
1
  • Since this is improper use of GROUP BY, you may need to set a flag having to do with "only full group by".
    – Rick James
    Commented Feb 13, 2017 at 18:22
1

If (A,B,C) is unique, at least for C=0.

select      min(a)                                      as a
           ,substr(min(concat(lpad(a,10,'0'),b)),11)    as b
           ,c                                           as c

from        mytable

group by    case c when 0 then a end
           ,case c when 0 then b end 
           ,c   

order by    a,b,c 

else -

select a,b,c from mytable where c = 0

union all

select      min(a)                                      as a
           ,substr(min(concat(lpad(a,10,'0'),b)),11)    as b
           ,c                                           as c

from        mytable

where       coalesce(c,-1) <> 0

group by    c   

order by    a,b,c 

--

+---+---+---+
| a | b | c |
+---+---+---+
| 0 | 1 | 4 |
+---+---+---+
| 4 | 5 | 6 |
+---+---+---+
| 5 | 6 | 0 |
+---+---+---+
| 6 | 5 | 0 |
+---+---+---+
| 9 | 5 | 0 |
+---+---+---+

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.