Q

Minimum value from several columns

I have a table Table1 and I would like to do something like this. What SELECT do I need?

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

Ugly, eh?

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

Simple, yes?

This was first published in January 2007

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close