Slightly Advanced SQL Aggregation

I hold myself as a programmer first, and a blogger second: so I hope you don't mind if I post a bit on a few SQL techniques I used this week.

Here's today's example:
I need a table of information containing a list of clients with their ID that shows counts by order type: 1,2,3, and OTHER.

Sounds like you should just do multiple select statements for each client right?



WRONG. This query is very hard to maintain and does at least 4 table scans per client. Yuck.

Try this instead:



Look at that maintainable easy to read code (unless you didn't know you could put a case statement inside of an aggregate function.) Now step up your ante, find the latest Order_Date split out by types using the mentioned technique using the Max function.

(hint, here's the answer:)



Sure, this is nothing special, but to someone who wants to increase the size of their Developer Toolbox, don't forget about speed and efficiency.

Have fun,
~Josh