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
Dig deeper on Oracle and SQL
Related Q&A from Rudy Limeback, SQL Consultant, r937.com
Read an example of an SQL case expression from our SQL expert Rudy Limeback.continue reading
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clausecontinue reading
Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback.continue reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.