SQL Number - Row_Number() - Allow Repeating Row Number

Question

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

You should use `RANK()` or `DENSE_RANK()` (as the case may be) instead of `ROW_NUMBER()`. — Pradeep Kumar, Aug 04 '15 at 04:06
I have tried this, but cannot seem to get the correct result with them. — Andy, Aug 04 '15 at 04:10
I'm unable to understand your requirement, could you please be specific by placing your current result and expected result ? — Ravi, Aug 04 '15 at 04:14
The MapPinNumber you showed above is the expected result you want? — Pradeep Kumar, Aug 04 '15 at 04:22
Yes. I'm trying to calculate that column based on the others. The final results must be ordered by Day ASC, ManualOrder ASC. — Andy, Aug 04 '15 at 04:30
Does this MapPinNumber need to be in a specific order, without gaps? Otherwise you could just calculate kind of a hash out of Lat/Lon. — Stefan Steinegger, Aug 04 '15 at 05:18

score 4 · Answer 1 · answered Aug 04 '15 at 04:47

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 r
LEFT JOIN CteBase b
    ON b.Day = r.Day
    AND b.Lat = r.Lat
    AND b.Lon = r.Lon
ORDER BY 
    r.Day, r.ManualOrder

ughai · Accepted Answer · 2015-08-04T12:46:31.203

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

MIN(ManualOrder)OVER(PARTITION BY Day,Lat,Lon) gets the minimum ManualOrder for a combination of Day, Lat and Lon.
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

Thank you so very much. Your answer was detailed and explained it, and you provided a short and simply solution. You saved me so much stress!! It's working flawlessly. — Andy, Aug 04 '15 at 15:20

score 2 · Answer 3 · edited Aug 04 '15 at 04:59

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

Select a.*, b.MapPinOrder from MyTable a
left 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
  ) b
on 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

score 0 · Answer 4 · answered Aug 04 '15 at 06:26

0

This would give you the result you need. However the MapPinNumber may not be in the exact same order as shown in your result.

SELECT *, 
    MapPinNumber = DENSE_RANK() OVER (PARTITION BY Day ORDER BY Lat, Lon) 
FROM Table1
ORDER BY Day, ManualOrder

answered Aug 04 '15 at 06:26

Pradeep Kumar

6,836
4
21
47

If it's not in the same order, then it clearly won't work for the OP's stated purpose (plotting a route). – Aug 04 '15 at 07:12

SQL Number - Row_Number() - Allow Repeating Row Number

4 Answers4