andre_ss6 andre_ss6 - 2 months ago 8
MySQL Question

Change filter depending on condition

I have a table with some hundreds of rows, and one Column "Color". This column "Color" can be 0 or 1. Sometimes, the column might be filled with more 1's than 0's, and vice-versa. What I want is: an expression that returns, if there are more 1's than 0's,

SELECT * FROM table WHERE Color = 0
, else
SELECT * FROM table WHERE Color = 1
. How can I do that?

Just to clarify: If there are more 1's than 0's, I want to select all the 0's. Else, do the contrary.

Dan Dan
Answer

You can use a query like this one to find out which value of Color is the most popular:

SELECT Color FROM `table` GROUP BY Color ORDER BY COUNT(*) DESC LIMIT 1 

Then, use this query as part of your WHERE condition to get the records with the other color.

SELECT * FROM `table` WHERE Color = 1 - (
    SELECT Color FROM `table` GROUP BY Color ORDER BY COUNT(*) DESC LIMIT 1 
)

SqlFiddle