0

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

1 Answer 1

1

What is sor_hdr? I think you need to drop the Partition by and replace row_number by rank

4
  • sor_hdr is supposed to be order_id. sorry, i fixed it
    – Stephen K
    Commented Jan 18, 2021 at 22:09
  • changing it to rank() made it so the number returned is the first row of each location grouping. i.e. if location Downtown starts on row 1 and goes through row 12, the value is 1. then coastal starts on 12 through 24 and so the value is 12. shouldn't it be 1, 2, 3, etc..?
    – Stephen K
    Commented Jan 18, 2021 at 22:12
  • 1
    ok, i see, change rank to dense_rank. At lease if i understand the requirements, that should do the trick Commented Jan 18, 2021 at 22:15
  • thanks! because there are instances where a customer could be the same distance from more than one location i need to use rank. dense_rank will make it assign the same rank to locations that are equidistant to the customer, which i don't want. if you want to edit your actual answer i'll mark it as solved :) thanks again!
    – Stephen K
    Commented Jan 18, 2021 at 22:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.