In the following query, you are getting the top 25 tags in the system. For that you use COUNT and GROUP BY to first group the records by the tags and counting them. In order to get the top 25, we are first ordering the records from highest to lowest by using the ORDER BY clause. Then with TOP 25 clause, we are plucking only the top 25 of the result set.
WITH toptags AS
(
SELECT TOP 25 tag, COUNT(tag) AS tagCount FROM tags
GROUP BY tag
ORDER BY tagCount DESC
)
SELECT * FROM toptags
ORDER BY tag
If we stopped with the first SELECT query, we would not have tags in the alphabetical order. For example, if we add the tag colum to the ORDER BY clause (as shown below), the second ordering happens within the set of tags with same count. Meaning, the tags with count 10 will be alphabetically ordered, but they will be below the tags with count, say, 15.
SELECT TOP 25 tag, COUNT(tag) AS tagCount FROM tags
GROUP BY tag
ORDER BY tagCount DESC, tag
So, by using the keyword WITH (in SQL Server 2005), you can place these results in a temporary table just for use with another query. So, in the second SELECT query above, we are ordering the results of the first query by tag. The final set of results will be in the alphabetical order; and you will get only the top 25 records.
One caveat here: if you omit the TOP clause in the first SELECT query (like the example below), you will have errors:
WITH toptags AS
(
SELECT tag, COUNT(tag) AS tagCount FROM tags
GROUP BY tag
ORDER BY tagCount DESC
)
SELECT * FROM toptags
ORDER BY tag
The error here is that without TOP, you can’t use ORDER BY in the WITH tables. You will see the following error:
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.
