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!
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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