0

Im using the rank function for a route order on my import. The script works perfectly fine except when I have to stops at a location (locationid,stored as a GUID).

This is how it currently looks
locationid Rank
0A0F4E97-699B-E011-B139-00188B47C528 1
0A0F4E97-699B-E011-B139-00188B47C528 2
110F4E97-699B-E011-B139-00188B47C528 3
110F4E97-699B-E011-B139-00188B47C528 4
0E0F4E97-699B-E011-B139-00188B47C528 5
0E0F4E97-699B-E011-B139-00188B47C528 6

This is how I would like it to look


locationid Rank
0A0F4E97-699B-E011-B139-00188B47C528 1
0A0F4E97-699B-E011-B139-00188B47C528 1
110F4E97-699B-E011-B139-00188B47C528 2
110F4E97-699B-E011-B139-00188B47C528 2
0E0F4E97-699B-E011-B139-00188B47C528 3
0E0F4E97-699B-E011-B139-00188B47C528 3

Any ideas?? :)

SELECT Street, StreetName, locationid, propertydatamap,   Rank = RANK() 
                  OVER (PARTITION BY propertydatamap 
                        ORDER BY propertydatamap, streetname, pkRPLRoutingID), locationitemtyperequestid
3
Contributors
3
Replies
4
Views
6 Years
Discussion Span
Last Post by Brillig
0

You could import your data into a temp table, do a select distinct with the rank into a second temp table, then join the two temp tables on locationid to assign the rank. Ugly, but it should work.

0

Yeah, the old way I was using CTE's to obtain it but it was exactly what you say "ugly"

0

It's a little difficult to tell without more details on your data model.

But shouldn't your rank function be more like

Rank = RANK()
OVER (PARTITION BY locationid
ORDER BY locationid)
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.