quote:

---

Originally posted by tariq2

Hello, thank you in advance for your help.
The following query does not quite produce the desired results.
I want a ranking by username (in descending order) of reports used. So the rank colun shiould be 1,2,3,4....
Currently it shows 1,1,1,1,1....
What am I doing wrong? Thank you


select UserName,SUM(reportcount) as rc,rank() over (partition by username order by sum(reportcount)desc) as 'rank' from View_BIUsers_ReportData where TreeYear = '2011'
and reportname = 'AOT' AND YEAR = '2011' group by UserName order by rc desc

---

SELECT
	UserName,
	SUM(reportcount) AS rc,
	RANK() OVER(PARTITION BY username ORDER BY SUM(reportcount)DESC) AS 'rank'
FROM
	View_BIUsers_ReportData
WHERE
	TreeYear = '2011'
	AND reportname = 'AOT'
	AND YEAR = '2011'
GROUP BY
	UserName
ORDER BY
	rc DESC