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.

  • the results order doesn't matter - Odd – Philᵀᴹ Feb 13 '17 at 15:05
  • What determines the order of the rows? – David דודו Markovitz Feb 13 '17 at 16:26
  • Is it significant that 014 was chosen instead of 754? Would it be ok to return 754 and drop 014? – Michael Green Feb 13 '17 at 18:21
  • @MichaelGreen yes its ok to return 754 and drop 014 – Nir Feb 13 '17 at 19:04
  • @DuduMarkovitz there is a simple key A B C – Nir Feb 13 '17 at 19:05
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)
  • 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 Feb 13 '17 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, privacy policy and cookie policy

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