SQL Number - Row_Number() - Allow Repeating Row Number

Here is my attempt using ROW_NUMBER:

SQL Fiddle

WITH CteRN AS(    SELECT *,        Rn = ROW_NUMBER() OVER(PARTITION BY Day ORDER BY ManualOrder),        Grp = ROW_NUMBER() OVER(PARTITION BY Day, Lat, Lon ORDER BY ManualOrder)    FROM tbl),CteBase AS(    SELECT *,        N = ROW_NUMBER() OVER(PARTITION BY Day ORDER BY ManualOrder)    FROM CteRN    WHERE Grp = 1)SELECT     r.ID, r.Day, r.ManualOrder, r.Lat, r.Lon,    MapPinNumber = ISNULL(b.N, r.RN)FROM CteRN rLEFT JOIN CteBase b    ON b.Day = r.Day    AND b.Lat = r.Lat    AND b.Lon = r.LonORDER BY     r.Day, r.ManualOrder

You can use aggregate function MIN with OVER to create your ranking groups and DENSE_RANK working on top of it like this.

Brief Explanation

1. MIN(ManualOrder)OVER(PARTITION BY Day,Lat,Lon) gets the minimum ManualOrder for a combination of Day, Lat and Lon.

2. DENSE_RANK() just sets this value as incremental values from 1.

SQL Fiddle

Sample Data

CREATE TABLE Tbl ([ID] int, [Day] varchar(3), [ManualOrder] int, [Lat] int, [Lon] int);INSERT INTO Tbl ([ID], [Day], [ManualOrder], [Lat], [Lon])VALUES    (1, 'Mon', 0, 36.55, 36.55),    (5, 'Mon', 1, 55.55, 54.44),    (3, 'Mon', 2, 44.33, 44.30),    (10, 'Mon', 3, 36.55, 36.55),    (11, 'Mon', 4, 36.55, 36.55),    (6, 'Mon', 5, 20.22, 22.11),    (9, 'Mon', 6, 55.55, 54.44),    (10, 'Mon', 7, 88.99, 11.22),    (77, 'Sun', 0, 23.33, 11.11),    (77, 'Sun', 1, 23.33, 11.11);

Query

;WITH CTE AS (SELECT *,GRP = MIN(ManualOrder)OVER(PARTITION BY Day,Lat,Lon) FROM Tbl)SELECT ID,Day,ManualOrder,Lat,Lon,DENSE_RANK()OVER(PARTITION BY Day ORDER BY GRP) AS RNFROM CTEORDER BY Day,ManualOrder

Output

ID  Day ManualOrder Lat Lon RN1   Mon 0   36.55   36.55   15   Mon 1   55.55   54.44   23   Mon 2   44.33   44.30   310  Mon 3   36.55   36.55   111  Mon 4   36.55   36.55   16   Mon 5   20.22   22.11   49   Mon 6   55.55   54.44   210  Mon 7   88.99   11.22   577  Sun 0   23.33   11.11   177  Sun 1   23.33   11.11   1

This may not be the most elegant solution, but it works:

Select a.*, b.MapPinOrder from MyTable aleft join  (  select distinct Day, Lat, Lon    , row_number()       over (partition by Day order by min(ManualOrder)) as MapPinOrder  from MyTable  group by Day, Lat, Lon  ) bon a.day = b.day   and a.lat = b.lat   and a.lon = b.lon  

Calculate the rows separately using the ordering you want, then join them back in to the full table.

SQL Fiddle