Count of unique items and count of all items in the same query

If you've scanned this blog you'll know of my fascination for subqueries. This thread at UtterAccess.com caught my eye today. The poster needs to count disticnt entries in one column, and all entries in another column, all grouped by the value in a third column.
Sounded to me like three queries. One for the each count and one to produce the final result. The poster, though, challenged the UAers to do it in one.
Van T. Dinh's post is a nice clean all in one query SQL solution. It's a good example of taking the two or three queries you would have done separately and building them up into one query, essentially by surrounding each with brackets and then putting it in the FROM clause like a table.
There are a couple of other posts here on my blog that put the whole subquery in the FROM clause.

• Writing a Count(Distinct) Query in Access
• More subqueries

** Post links cleaned up 2011-09-05 **

Returning Records around a specified ranked Record

Here's a really neat post that uses subqueries to return records "near" a particular record when a bunch of data is ranked.

Quote:
Sometimes, for example when you are dealing with a large table of data that you wish to have ranked, such as a sports league table, you may wish to return the ranking of a particular record, together with some records ranked just before this record, and some just after.