One-to-Many TSQL Query

Question

Post reply

One-to-Many TSQL Query

rian67

SSC Enthusiast

Points: 187
More actions
November 1, 2010 at 3:30 pm

#240538

I have billing customers which has multiple shipping addresses. Unfortunately, we did a poor job of data governance and so we have the same shipping address repeated multiple times. For example, we have instance where a customer has 20+ shipping address records for the same shipping zip code.
I would like to get one customer record per address per zip code. I have created a table which has all the customers with all the multiple shipping addresses and also with the last time an order was shipped to that shipping address.
I would like to create a query where I can only get one record per zip code per customer and that record needs to be the latest order date.
Can someone give my some insight? Thanks

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95460 More actions · Answer 1

select top 1 ... order by order_date desc

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

LutzM SSC Guru Points: 107049 More actions · Answer 2

... or you could use a subquery to return the ID per per zip code per customer with max(order date).

Use this subquery to join back to your original table.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Andreas Goldman Old Hand Points: 313 More actions · Answer 3

Or a CTE like below:

;WITH CUSTADDR

AS

(

SELECT

CUSTOMER_ID,

ZIPCODE,

ADDR,

ROW_NUMBER() OVER(PARTITION BY CUSTOMER_ID, ZIPCODE ORDER BY SHIPPING_DATE DESC) AS RowNr

FROM CUSTOMERADDRESS -- Your table

)

SELECT

CUSTOMER_ID,

ZIPCODE,

ADDR

FROM CUSTADDR

WHERE RowNr = 1

Andreas Goldman