Error in Rankfunction - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Error in Rankfunction

Author

Topic

lols
Posting Yak Master

174 Posts

Posted - 2009-03-28 : 11:16:25

I have an Enroll Table

EnrollID
AgentID
EnrollmentDate

I want to list how many Agents did how many Enrollments using the Rankfunction in SQL Server 05/08. How to do?

Output needs to be :

Position      AgentId     CountEnroll
1              62          29
2              55          22
3              34          19

A helpful user visakh suggested me this query:

select agentid, countEnroll,,rank() over( partition by agentid order by countEnroll desc) as rnk from 
( select agentid,count(EnrollID) as countEnroll from urtable group by agentid) t

But when I run query, it gives me Rank = 1 for all rows. Please say what tocorrecT?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-28 : 15:08:29

SELECT RANK() OVER (ORDER BY CountEnroll DESC) AS Position,
AgentID, CountEnroll
FROM (
SELECT AgentID, COUNT(*) AS CountEnroll
FROM Enroll Group BY AgentID
) AS d

E 12°55'05.63"
N 56°04'39.26"

lols
Posting Yak Master

174 Posts

Posted - 2009-03-29 : 13:25:31

East or West, Peso is the best :) Thanks once again

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources