{infiniteZest}
// Articles. Tutorials. Utilities.
Home  |   Search  |   Login  
Categories Skip Navigation Links
New / All
AJAX
Apple
ASP.NET
.NET
Git
Google / Android
Python / IronPython
Miscellaneous
SQL Server
Using WITH in SQL Server 2005 with two order by clauses
Summary
Selecting the top x records from a query using one order by clause and then ordering the records with in that set of records becomes easy with the WITH keyword in SQL Server 2005. This article looks at creating a temporary table and then querying on that table.
 
Table of Contents
 

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.

Bookmark and Share This

More Articles With Similar Tags
SQL Server 2005 provides a way to obtain a range of records obtained from a SELECT statement using row_number. This article has a stored procedure that gives the records of a given pagesize from given pagenumber.
About  Contact  Privacy Policy  Site Map