2

Using MySQL, how can i SELECT all rows whose values are equals ? For instance, suppose a SELECT of a given table as follows

COLUMN_A   COLUMN_B   COLUMN_C ... COLUMN_N
       1          2          4            7
       6          3          5            6
       5          5          5            5
       7          1          3            4
       3          3          3            3

I need a SELECT which should return

COLUMN_A   COLUMN_B   COLUMN_C ... COLUMN_N
       5          5          5            5
       3          3          3            3
3
  • 2
    where (column_a = column_b and column_a = column_c ...) Commented Mar 31, 2015 at 1:11
  • 2
    Non-intuitive approach: where least(column_a, column_b, ...) = greatest(column_a, column_b, ...) Commented Mar 31, 2015 at 8:32
  • 1
    Oh, and be wary of null. Commented Mar 31, 2015 at 8:33

2 Answers 2

4
SELECT
   column_a
  ,column_b
  ,column_c
  ,column_d
FROM mytable
WHERE column_a = column_b
AND   column_a = column_c
AND   column_a = column_d

ETC.

SQL Fiddle Link

My Notes

CREATE TABLE mytable
(
column_a int,
column_b int,
column_c int,
column_d int
);

INSERT INTO mytable VALUES (1,2,4,7);
INSERT INTO mytable VALUES (6,3,5,6);
INSERT INTO mytable VALUES (5,5,5,5);
INSERT INTO mytable VALUES (7,1,3,4);
INSERT INTO mytable VALUES (3,3,3,3);
2
  • Updated answer as my previous one was the wrong approach. It should be one column (column_a) we are comparing to all columns. Not a pair of columns as each pair may be equal to the pair, but not to the entire row.
    – Glen Swan
    Commented Mar 31, 2015 at 1:19
  • Yes, you are right. Equality is transitive so we just need to compare our values against a given reference. Congrats!!! Commented Mar 31, 2015 at 2:41
2

You could unpivot the data. I'm going to add an surrogate ID column to your table because this will make reconciliation easier. The unpivoted data will look like this:

id  SourceCol SourceVal
1   A         3
1   B         3
1   C         3
...
1   N         3
2   A         5
2   B         5
2   C         5
...
2   N         5

Then you group by ID and SourceVal, and only return the sets with all matches

select
    ID
from UnPivotedTable
group by ID, SourceVal
having count(*) = N     -- N = however many columns you have in the source table.

The result set can be joined back to the original table to get the full rows. If you don't add the surrogate ID you'll have to join back to the source table on every column:

select
    col_A ... Col_N
from Source as s
inner join UnPivotResult as u -- this is the result set from the previous query
on s.col_A = u.SourceVal
and s.col_B = u.SourceVal
...
and s.col_N = u.SourceVal

.. which is likely to be ugly.

I'm not entirely au fait with MySQL syntax so apologies if I'm off by a few characters.

1
  • Yes, it also works, but MySQL PIVOT syntax is by far most difficult than a single select. Thank you anyway. Commented Mar 31, 2015 at 2:43

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