Search code examples
sqlsql-serversql-server-2008row-numberdense-rank

SQL Number - Row_Number() - Allow Repeating Row Number

I'm using SQL Server 2008. I have this data returned in a query that looks pretty much like this ordered by Day and ManualOrder...

ID   Day  ManualOrder  Lat     Lon    
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

What I'm trying to do is get this data ordered by Day, then ManualOrder...but I'd like a row counter (let's call it MapPinNumber). The catch is that I'd like this row counter to be repeated once it encounters the same Lat/Lon for the same day again. Then it can continue on with the next row counter for the next row if it's a different lat/lon. We MUST maintain Day, ManualOrder ordering in the final result.

I'll be plotting these on a map, and this number should represent the pin number I'll be plotting in ManualOrder order. This data represents a driver's route and he may go to the same lat/lon multiple times during the day in his schedule. For example he drives to Walmart, then CVS, then back to Walmart again, then to Walgreens. The MapPinNumber column I need should be 1, 2, 1, 3. Since he goes to Walmart multiple times on Monday but it was the first place he drives too, it's always Pin #1 on the map.

Here's what I need my result to be for the MapPinNumber column I need to calculate. I've tried everything I can think of with ROW_NUMBER and RANK, and going insane! I'm trying to avoid using an ugly CURSOR.

ID   Day  ManualOrder  Lat     Lon     MapPinNumber
1    Mon  0            36.55   36.55   1
5    Mon  1            55.55   54.44   2
3    Mon  2            44.33   44.30   3
10   Mon  3            36.55   36.55   1
11   Mon  4            36.55   36.55   1
6    Mon  5            20.22   22.11   4
9    Mon  6            55.55   54.44   2
10   Mon  7            88.99   11.22   5
77   Sun  0            23.33   11.11   1
77   Sun  1            23.33   11.11   1
Solution

  • 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 RN
FROM CTE
ORDER BY Day,ManualOrder

    Output

    ID  Day ManualOrder Lat Lon RN
1   Mon 0   36.55   36.55   1
5   Mon 1   55.55   54.44   2
3   Mon 2   44.33   44.30   3
10  Mon 3   36.55   36.55   1
11  Mon 4   36.55   36.55   1
6   Mon 5   20.22   22.11   4
9   Mon 6   55.55   54.44   2
10  Mon 7   88.99   11.22   5
77  Sun 0   23.33   11.11   1
77  Sun 1   23.33   11.11   1