I have a table Table1 like this:
ID A B C 1 10 26 20 2 20 12 14 3 9 20 45
I would like to do something like:
select ID from Table1 where min value from(A,B,C) < D
So in my example for D=11, I'll get ID=1,3. What SELECT do I need?
The ideal situation would be if your database system had a function to select the lowest value from a list of parameters. MySQL, PostgreSQL and Oracle (and perhaps others) support the GREATEST and LEAST functions, so you could use LEAST like this:
select ID from Table1 where least(A,B,C) < 11
If you're using some other database, you might have to hardcode the logic yourself, perhaps something like this:
select ID from Table1 where case when A <= B and A <= C then A when B <= A and B <= C then B else C end < 11
Of course, if you had structured your table differently, then the query would have been a lot easier.
ID col data 1 A 10 1 B 26 1 C 20 2 A 20 2 B 12 2 C 14 3 A 9 3 B 20 3 C 45
Now you can use the MIN aggregate function:
select ID from Table1 group by ID having MIN(data) < 11
This was first published in January 2007