MS SQL Server Interview Question:

Can Group Functions Be Used in the ORDER BY Clause in MS SQL Server?

Submitted by: Administrator

If the query output is aggregated as groups, you can sort the groups by using group functions in the ORDER BY clause. The following statement returns the maximum "counts" in each group, determined by a unique combination of tag and year. The group output is sorted by the maximum "counts" in each group in ascending order:

SELECT tag, YEAR(created), MAX(counts)
FROM ggl_links GROUP BY tag, YEAR(created)
ORDER BY MAX(counts)
GO<pre>
tag year(created) max(counts)
DEV 2006 120
DBA 2006 390
DEV 2004 439
SQA 2007 728
SQA 2003 828
DBA 2005 960
DBA 2007 972</pre>
Submitted by: Administrator