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