Ranking

[atltechie]

Hello,

I am trying to rank subscribers calls based on minutes. I want the rank to be within the each subscriber not based on every call every call in the table. I used the following query and it seems to be ranking the entire table based on minutes. Any ideas???

SELECT C1.SUBSCRIBER, C1.CALL_TO_NUMBER, C1.PRICE_PLAN, C1.SumOfMinutes,
(SELECT COUNT (SumOfMinutes)
FROM [CALL SUM MINUTES] AS C2
WHERE C2.SumOfMinutes >= C1.SumOfMinutes
AND C2.CALL_TO_NUMBER = C1.CALL_TO_NUMBER
AND C2.SUBSCRIBER = C1.SUBSCRIBER
and c2.PRICE_PLAN = C1.PRICE_PLAN) as Rank
FROM [CALL SUM MINUTES] AS C1;

 

[sfreeman]

There is no GroupBy.

Try grouping by Subscriber.

Sam_F
"90% of the problem is asking the right question.

 

[r937]

nope, GROUP BY is not required

the given query looks okay to me

atltechie, can you give some sample data where it seems to be ranking incorrectly?

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[PHV]

Something like this ?
SELECT C1.SUBSCRIBER, C1.CALL_TO_NUMBER, C1.PRICE_PLAN, C1.SumOfMinutes,
(SELECT COUNT (SumOfMinutes)
FROM [CALL SUM MINUTES] AS C2
WHERE C2.SumOfMinutes >= C1.SumOfMinutes
AND C2.SUBSCRIBER = C1.SUBSCRIBER) as Rank
FROM [CALL SUM MINUTES] AS C1;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244