Member Avatar for 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
  • 3 Contributors
  • 3 Replies
  • 89 Views
  • 1 Week Discussion Span
  • Latest Post Latest Post by Brillig
Member Avatar for 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.

Member Avatar for moone009

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

Member Avatar for 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)
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.