Null is Odd…or Things I Used to Know about SQL Aggregate Functions

Brendan McCullum recently played his final Test for New Zealand.
That’s something of an understatement. In his last game he made a century in a mere 54 balls, a feat unmatched in 139 years of test cricket.
From the outside looking in, it seemed that McCullum had come to realise something he’d always known. Playing cricket is supposed to be fun.
What’s more, you can consider yourself quite fortunate if you get paid for doing something you enjoy, especially when that something is hitting a ball with a stick.

With the help of Mr McCullum, what follows will serve to remind me of something I’ve always known but may forget from time to time.
In my case, it’s the fact that NULL is odd. This is especially true when it comes to basic SQL aggregation functions.
Continue reading

How long is a (piece of) String. Cricketing Greats and the length function

My Dad gave me a book recently – the 100 Greatest Cricketers. As well as selecting what – in his opinion at least – were the 100 finest exponents of the game, the author also decided to rank them in order from 1 to 100.
At this point, for those who don’t know, I should perhaps explain that Cricket is one of those games given by the English to the rest of the world….to prove what sporting losers the English could be.
For any Australians reading, to whom this statement may ring hollow given their teams current travails, fret not. Historically, England’s brief ascendancy the battle for the Ashes tends to come to a juddering halt when least expected, usually in the form of a 4-0 thrashing ( think 1958-59, 1989).

Anyway, back to the book. Comparing players across different eras is hard enough – just how would you evaluate the relative merits of Sachin Tendulkar and Sir Jack Hobbs, for example ?
But comparing the relative merits of a batsmen and bowler who were contemporaries in the same team is equally problematic. Who is the greater cricketer out of Dennis Lillee and Greg Chappell, or Malcolm Marshall and Viv Richards ?

All of this brings to mind the saying, “how long is a piece of string”.
In SQL, at least, we do have an answer to this question…or do we ? Continue reading

More Nefarious Nullable Nonsense – Coalesce

OK so it’s not really nefarious but I couldn’t think of another word beginning with “N”.
You may also be relieved to know that this is the dying parp of the vuvuzela on this blog.

This is really just a bit about coalesce – a little used but extremely useful SQL function.

Let’s get the World Cup example out of the way. Here’s a table containing details of the four semi-finalists from this year’s tournament.

Conveniently enough, they consist of two previous winners, one with a best performance as beaten-finalists, and one with a previous best of 4th. Too good an opportunity to miss…. Continue reading

Gullible about Nullable – Stuff I thought I knew…and didn’t

Taking a break from the advanced mathematical theory required to work out all the permutations that will allow England to qualify from their group, I thought that, by way of light relief, I’d have a look at NULL in SQL.

I was recently disabused of a long-held notion that NULL will always evaluate to TRUE. I can’t remember for the life of me where I acquired this delusion. It’s never been particularly relevant to me as, whenever I’m likely to be faced with such a situation, I simply use the NVL function.

I’m wondering – is it just me – is that something I’ve mis-remembered from my days as an Oracle neophyte back when the world was young? Continue reading