Ranking

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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;
 
What you've posted looks as if it has the correct setup, but I would fully
qualify the field name in the subquery's COUNT:

SELECT C1.SUBSCRIBER, C1.CALL_TO_NUMBER, C1.PRICE_PLAN, C1.SumOfMinutes,
(SELECT COUNT (C2.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;
 
Thanks Ken!!

I thought it would work but it seems to be rankinig all calls by
SumOfMinutes. Do you know of another way to get a SumOfMinutes ranking within
each subscriber?

Ken Snell said:
What you've posted looks as if it has the correct setup, but I would fully
qualify the field name in the subquery's COUNT:

SELECT C1.SUBSCRIBER, C1.CALL_TO_NUMBER, C1.PRICE_PLAN, C1.SumOfMinutes,
(SELECT COUNT (C2.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;

--

Ken Snell
<MS ACCESS MVP>

atltechie said:
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;
Click to expand...
Click to expand...
 
OK -

After looking at your query again, I am not clear why it's not giving what
you seek; therefore, can you post some sample data that are in the CALL SUM
MINUTES table/query and post an example of what the query should show?
--

Ken Snell
<MS ACCESS MVP>



atltechie said:
Thanks Ken!!

I thought it would work but it seems to be rankinig all calls by
SumOfMinutes. Do you know of another way to get a SumOfMinutes ranking
within
each subscriber?

Ken Snell said:
What you've posted looks as if it has the correct setup, but I would
fully
qualify the field name in the subquery's COUNT:

SELECT C1.SUBSCRIBER, C1.CALL_TO_NUMBER, C1.PRICE_PLAN, C1.SumOfMinutes,
(SELECT COUNT (C2.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;

--

Ken Snell
<MS ACCESS MVP>

atltechie said:
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;
Click to expand...
Click to expand...
Click to expand...
 
sample data is as follows

Call Sum Minutes table
Account SUBSCRIBER_NO CALL_TO_NUMBER Minutes
223 222222 6122323 213.23
223 222222 9522322 100.02
304 333333 2324444 93.23
304 333333 5632435 23.23
304 333333 8535564 12.22


I want to rank the above data as follows

Account SUBSCRIBER_NO CALL_TO_NUMBER Minutes Rank
223 222222 6122323 213.23 1
223 222222 9522322 100.02 2
304 333333 2324444 23.23 2
304 333333 5632435 93.23 1
304 333333 8535564 12.22 3


Thanks
Ken Snell said:
OK -

After looking at your query again, I am not clear why it's not giving what
you seek; therefore, can you post some sample data that are in the CALL SUM
MINUTES table/query and post an example of what the query should show?
--

Ken Snell
<MS ACCESS MVP>



atltechie said:
Thanks Ken!!

I thought it would work but it seems to be rankinig all calls by
SumOfMinutes. Do you know of another way to get a SumOfMinutes ranking
within
each subscriber?

Ken Snell said:
What you've posted looks as if it has the correct setup, but I would
fully
qualify the field name in the subquery's COUNT:

SELECT C1.SUBSCRIBER, C1.CALL_TO_NUMBER, C1.PRICE_PLAN, C1.SumOfMinutes,
(SELECT COUNT (C2.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;

--

Ken Snell
<MS ACCESS MVP>

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;
Click to expand...
Click to expand...
Click to expand...
 
sample data is as follows

Call Sum Minutes
Account SUBSCRIBER CALL_TO_NUMBER Minutes
223 222222 6122323 213.23
223 222222 9522322 100.02
304 333333 2324444 23.23
304 333333 5632435 93.23
304 333333 8535564 12.22


I want to rank the above data as follows

Account SUBSCRIBER CALL_TO_NUMBER Minutes Rank
223 222222 6122323 213.23 1
223 222222 9522322 100.02 2
304 333333 2324444 23.23 2
304 333333 5632435 93.23 1
304 333333 8535564 12.22 3


Thanks!!

Ken Snell said:
OK -

After looking at your query again, I am not clear why it's not giving what
you seek; therefore, can you post some sample data that are in the CALL SUM
MINUTES table/query and post an example of what the query should show?
--

Ken Snell
<MS ACCESS MVP>



atltechie said:
Thanks Ken!!

I thought it would work but it seems to be rankinig all calls by
SumOfMinutes. Do you know of another way to get a SumOfMinutes ranking
within
each subscriber?

Ken Snell said:
What you've posted looks as if it has the correct setup, but I would
fully
qualify the field name in the subquery's COUNT:

SELECT C1.SUBSCRIBER, C1.CALL_TO_NUMBER, C1.PRICE_PLAN, C1.SumOfMinutes,
(SELECT COUNT (C2.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;

--

Ken Snell
<MS ACCESS MVP>

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;
Click to expand...
Click to expand...
Click to expand...
 
Your table is using Minutes as the field name, not SumOfMinutes.

Replace SumOfMinutes with Minutes in your query and try it again.

--

Ken Snell
<MS ACCESS MVP>

atltechie said:
sample data is as follows

Call Sum Minutes table
Account SUBSCRIBER_NO CALL_TO_NUMBER Minutes
223 222222 6122323 213.23
223 222222 9522322 100.02
304 333333 2324444 93.23
304 333333 5632435 23.23
304 333333 8535564 12.22


I want to rank the above data as follows

Account SUBSCRIBER_NO CALL_TO_NUMBER Minutes Rank
223 222222 6122323 213.23 1
223 222222 9522322 100.02 2
304 333333 2324444 23.23 2
304 333333 5632435 93.23 1
304 333333 8535564 12.22 3


Thanks
Ken Snell said:
OK -

After looking at your query again, I am not clear why it's not giving
what
you seek; therefore, can you post some sample data that are in the CALL
SUM
MINUTES table/query and post an example of what the query should show?
--

Ken Snell
<MS ACCESS MVP>



atltechie said:
Thanks Ken!!

I thought it would work but it seems to be rankinig all calls by
SumOfMinutes. Do you know of another way to get a SumOfMinutes ranking
within
each subscriber?

:

What you've posted looks as if it has the correct setup, but I would
fully
qualify the field name in the subquery's COUNT:

SELECT C1.SUBSCRIBER, C1.CALL_TO_NUMBER, C1.PRICE_PLAN,
C1.SumOfMinutes,
(SELECT COUNT (C2.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;

--

Ken Snell
<MS ACCESS MVP>

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;
Click to expand...
Click to expand...
Click to expand...
 
Back
Top