liwuen liwuen - 1 year ago 59
MySQL Question

Query for selecting columns with repeated values

I have a table with multiple records and multiple columns, something like:

Name |Color |Price |Description |Code
Item1 |Blue |$15.00 |ABCD |Code1
Item2 |Blue |$25.00 |ABCD |Code2
Item3 |Blue |$35.00 |ABCD |Code2
Item4 |Blue |$15.00 |ABCD |Code1
Item5 |Blue |$15.00 |ABCD |Code1

I want to get, by using a query, the columns that have the same values throughout all the records, something like

Color |Description
Blue |ABCD
Blue |ABCD
Blue |ABCD
Blue |ABCD
Blue |ABCD

Is there any easy way to do that? I've been thinking on a way to get this but no success so far. Can anyone help me to do this? Thanks!

Answer Source

In mysql (and oracle) you can use a subselect .. group bu and having

select * from my_table 
where (color, decription) in (select color, description 
                      from my_table 
                      group by color, description 
                      having count(*) >1)

Otherwise if you don't know id advance which colums are going to satisfy the criteria you can build a dinamic select for testing each column .

select my_column from my_table 
group by my_column
having count(*) >1

if the related select .. group by adn count(*) is >1 you have repeeated value

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download