Creating row number for multiple columns? I have the below data, and I want to only take one Location per Order ID ordered by the distance. This data set takes customer zips and compares to store zips and then returns the distance. I want to only choose the store they're closest to.
I have this so far:
SELECT 
*
FROM (
    SELECT 
    t.*,
    row_number() over(PARTITION BY orderid ORDER BY dts) rn
    FROM (
        SELECT 
        location,
        orderid,
        group1,
        group2,
        group3,
        group4,
        group5,
        custid,
        dts,
        sum(qty) AS units,
        sum(bsk) AS demand
        FROM osfdist
        GROUP BY 1,2,3,4,5,6,7,8,9
    ) t 
) a 

But this just starts the increment at 1 for the first row ordered by Distane and then increments to the total number of rows. I want it to say:
Downtown    1
Downtown    1
Downtown    1
Downtown    1
Coastal     2
Coastal     2
Coastal     2
Coastal     2

etc, so I can select where row_number=1 and only select the Downtown records.




Location
OrderID
Group 1
Group 2
Group 3
Group 4
Group 5
Customer ID
Distance
Qty
Sales




Downtown
1
FOOTWEAR
SHOES
SHOES (LOW)
M
RUN
abc123
8.724497523
1
90


Downtown
1
APPAREL
PANTS
PANTS (1/1)
F
FOO
abc123
8.724497523
1
22.5


Downtown
1
FOOTWEAR
SHOES
SHOES (LOW)
U
ORI
abc123
8.724497523
1
55


Downtown
1
APPAREL
SHORTS
SHORTS
M
TRA
abc123
8.724497523
3
50


Downtown
1
APPAREL
PANTS
TRACK PANT
F
ORI
abc123
8.724497523
1
35


Downtown
1
APPAREL
PANTS
TRACK PANT
M
ORI
abc123
8.724497523
1
35


Downtown
1
FOOTWEAR
SHOES
SHOES (LOW)
M
ORI
abc123
8.724497523
1
65


Downtown
1
APPAREL
JACKETS
LIGHT JACKET
F
OUT
abc123
8.724497523
2
100


Downtown
1
APPAREL
PANTS
PANTS (1/1)
M
TRA
abc123
8.724497523
1
27.5


Downtown
1
FOOTWEAR
SANDALS/SLIPPERS
SLIDES
M
RUN
abc123
8.724497523
1
17.5


Downtown
1
APPAREL
TIGHTS
TIGHT LONG
F
TRA
abc123
8.724497523
2
35


Coastal
1
APPAREL
PANTS
TRACK PANT
F
ORI
abc123
8.888442956
1
35


Coastal
1
FOOTWEAR
SHOES
SHOES (LOW)
M
RUN
abc123
8.888442956
1
90


Coastal
1
FOOTWEAR
SANDALS/SLIPPERS
SLIDES
M
RUN
abc123
8.888442956
1
17.5


Coastal
1
FOOTWEAR
SHOES
SHOES (LOW)
U
ORI
abc123
8.888442956
1
55


Coastal
1
APPAREL
PANTS
TRACK PANT
M
ORI
abc123
8.888442956
1
35


Coastal
1
APPAREL
PANTS
PANTS (1/1)
M
TRA
abc123
8.888442956
1
27.5


Coastal
1
FOOTWEAR
SHOES
SHOES (LOW)
M
ORI
abc123
8.888442956
1
65


Coastal
1
APPAREL
JACKETS
LIGHT JACKET
F
OUT
abc123
8.888442956
2
100


Coastal
1
APPAREL
SHORTS
SHORTS
M
TRA
abc123
8.888442956
3
50


Coastal
1
APPAREL
PANTS
PANTS (1/1)
F
FOO
abc123
8.888442956
1
22.5


Coastal
1
APPAREL
TIGHTS
TIGHT LONG
F
TRA
abc123
8.888442956
2
35
What is sor_hdr?
I think you need to drop the Partition by and replace row_number by rank