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

    Login

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

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top