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 |
Best Answer
What is sor_hdr? I think you need to drop the Partition by and replace row_number by rank