Rank function

[moone009]

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

[BitBlt]

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.

[moone009]

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

[Brillig]

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)