Just a note, this data is all off the top of my head so it may well be either (a) factually wrong or (b) further evidence that I should really get out more. Either way, it allows me to illustrate the use of COALESCE.

If we want to query this table to find the years that each team achieved their best performance, there’s a few options.

First off, especially for those of us who remember the days before Oracle started appending random letters of the alphabet to it’s database versions…

If you’re looking to return the first non-null value of more than two columns then COALESCE does the job with less typing than anything else.

Incidentally, just in case you’re wondering if the excitement of watching the crowning of only the third new nation to win the World Cup in my lifetime has caused amnesia on my part, here’s what happened when I tried the same query using CASE :

Now I admit it’s possible that I may have not got the syntax quite right ( I tried it with and without brackets, the result is the same). However, it does seem – on 10g XE at least – that CASE isn’t really in the mood to – as it were – play ball.

That awful pun seems like a good point at which to finish, so I’ll just say that I’ll try the CASE case on 11g at some point.
In the meantime, feel free to point and laugh if you spot the elementary mistake.

you are officially a clever-clogs and should definitely put in for that pay-rise !
I’ve had another mess around with this stuff and I’ll post a follow-up.

In the meantime, I’ve made some minor corrections to this post ( like writing proper sentences). This is obviously a classic illustration of why you shouldn’t post anything at the end of a night on the beer. Oh well, if you can’t be a shining example, you can always be a terrible warning.

SELECT team, best,
CASE WHEN winners is not null THEN winners
WHEN runner_up is not null THEN runner_up
WHEN third is not null THEN third
WHEN fourth is not null then fourth
ELSE null
END years
FROM final_four
/