Thursday, June 22, 2006

[sql] Finding top 5 within group

Cool SQL trick from Todd. With mods for my own purpose.


SELECT A.dt, A.tokengroup, A.token, A.num
FROM #tmp1 AS A, #tmp1 AS B
WHERE A.dt = B.dt
AND A.tokengroup = B.tokengroup
AND A.token <> B.token and A.num <>
GROUP BY A.dt, A.tokengroup, A.token, A.num
HAVING COUNT(*) <= 5
ORDER BY A.dt, A.tokengroup, A.token, A.num desc

No comments:

Post a Comment