Tao Klerks (3/18/2008)
SELECT TOP 1 dept_id, nFROM (
SELECT dept_id, count(*) AS n
FROM employee
GROUP BY dept_id
) AS a
ORDER BY n DESC
This is essentially the same as:
;with Depts (ID, Employees) as
(select dept_id, count(*)
from dbo.employee
group by dept_id)
select top 1 ID
from depts
order by employees desc
Either one is better than what was presented in the article. The two examples above will have the same query plan (in my tests and use).
It's not a question of performance of derived tables vs performance of CTEs. Both work the same way.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon