Subqueries

I am fascinated by subqueries and keep finding things on-line that I didn't know would work. Some of the really elegant subqueries I've created have turned out to be reall performance problems. Others work fine and result in an all-in-one solutions that seems "smarter" and mean less components to manage.

I found one today in a post on the Access team blog. Here's one of the queries from the article:

SELECT C.Color, Sum(C.Value) AS Total, T2.N
  FROM
    (SELECT T.Color, Count(T.Color) AS N
     FROM
      (SELECT DISTINCT Color, Count(*) AS N
       FROM tblColors GROUP BY Color) AS T
       GROUP BY T.Color) AS T2
  INNER JOIN tblColors AS C
  ON T2.Color = C.Color 
  GROUP BY C.Color, T2.N;

This is for count of distinct items. See the blog post for more.