Link to home
Start Free TrialLog in
Avatar of jwa1107
jwa1107

asked on

increment a rank/count on identical record values?

I've run into a `new` problem from the ranking query originally posted here:
https://www.experts-exchange.com/questions/21333108/reset-IDENTITY-at-each-change-in-specific-record-group.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!
ASKER CERTIFIED SOLUTION
Avatar of Brian Crowe
Brian Crowe
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of antonbijl
antonbijl

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