Ask the Expert

Select another row if first choice is not available

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?

    Requires Free Membership to View

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' )
    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

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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: