Solved

# increment a rank/count on identical record values?

Posted on 2005-03-23
Medium Priority
507 Views
I've run into a `new` problem from the ranking query originally posted here:
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21333108.html

the current state of the query is this:
SELECT [Agt],[Terr],[Pol],
(Select Count(*)+1 from tablename Where Agt = t.Agt and (Pol > t.Pol or (Pol=t.Pol and Terr < t.Terr and Pol > t.Pol))) As "Rank"
FROM tablename t
ORDER BY Agt,Rank,Terr,Pol

prior data sets looked and worked great:
AGT        TERR POL   RANK
5010AB    12     70       1
5010AB    11     17       2
5010AB    10     7        3
5010AB    13     1        4
6610CC    22     59       1
6610CC    21     57       2
6610CC    10     37       3

but a new conundrum came up:
AGT        TERR POL   RANK
5010AB    12     70       1
5010AB    10     17       2
5010AB    11     17       2
5010AB    13     5        3

while I desire this as results:
AGT        TERR POL   RANK
5010AB    12     70       1
5010AB    10     17       2
5010AB    11     17       3
5010AB    13     5        4

how can I modify the above query to still rank in descending order,
and where POL is identical
rank the lowest TERR first
and increment the RANK on the next TERR, etc.?

thanks!
0
Question by:jwa1107

LVL 34

Accepted Solution

Brian Crowe earned 2000 total points
ID: 13612101
SELECT [Agt],[Terr],[Pol],
(Select Count(*)+1 from tablename Where Agt = t.Agt and (Pol > t.Pol or (Pol=t.Pol and Terr < t.Terr))) As "Rank"
FROM tablename t
ORDER BY Agt,Rank,Terr,Pol
0

LVL 4

Expert Comment

ID: 13612118
Maybe I'm missing something, but (Pol=t.Pol and Terr < t.Terr and Pol > t.Pol) will never be true because if Pol=t.Pol and Pol>t.Pol are mututally exclusive. I'm not 100% sure I understand fully what you're trying to accomplish but I think you need to remove that last Pol>t.Pol, like so:

SELECT [Agt],[Terr],[Pol],
(Select Count(*)+1 from tablename Where Agt = t.Agt and (Pol > t.Pol or (Pol=t.Pol and Terr < t.Terr))) As "Rank"
FROM tablename t
ORDER BY Agt,Rank,Terr,Pol
0

## Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.