Q

Select another row if first choice is not available

I have a table with two columns, col1 and col2. I would like, for a given col1, to return the record whose col2 is CM, but if col2 as CM is not available, then return the record whose col2 is ML. Can this be achieved using an SQL query?

I have a table with two columns, col1 and col2. The data in the table is as follows:

col1 col2
4587 TP
4587 ML
4587 CM
3509 TP
3509 BL
3509 ML

Notice that for 4587, CM is available. For 3509, CM is not available. I would like, for a given col1, to return the record whose col2 is CM, but if col2 as CM is not available, then return the record whose col2 is ML. Can this be achieved using an SQL query?

Yes, there are several ways to do it. Here's one way that very closely resembles the way you stated the problem:

select col1, col2
  from yourtable
 where col2 = 'CM'
union all
select col1, col2
  from yourtable as t1
 where col2 = 'ML'   
   and not exists
       ( select *
           from yourtable
          where col1 = t1.col1
            and col2 = 'CM' )

This method employs a UNION in which the first SELECT returns CM rows, while the second SELECT returns ML rows for each col1 that doesn't have a CM row. Notice that the NOT EXISTS subquery is correlated. In my opinion, this solution is a good one because its purpose is clear and easy to understand.

Now here's another way to do it:

select col1, min(col2)
  from yourtable
 where col2 in ( 'CM', 'ML' )
group
    by col1

This solution uses grouping and the MIN aggregate function, and relies on the fact that CM collates lower than ML. It works perfectly well, and it's more efficient than the first solution, too. However, in my opinion, it's not as good. What happens if we need to return a third column from the row? Suddenly it no longer works at all.

This was first published in December 2005
This Content Component encountered an error

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:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close