Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Russ110
Starting Member
1 Post |
 Posted - 2011-07-26 : 18:39:25
|
| HiI'm new to SQL and have hit a brick wall. I have a table Customer_Contacts containing (amongst other fields):Customer_IDContact_DateContact_Seq NoThe table holds details of customer contacts. Each customer can be contacted on one or more dates, and can have one or more contacts on the same day. (The Contact_Seq_No is populated during our overnight batch run when the table is updated from the source system, and is a sequential number for records processed during that run; it is independent of the Customer_ID and Contact_Date, but should let me work out the latest contact on a day with multiple contacts).I need to work out the most recent contact information for each Customer_ID, and if multiple contacts exist on the same day, I want to pick up only the latest one.1. I want to get the max(Contact_Date) for each Customer_ID.2. Then I want to get the max(Contact_Seq_No) for each of the results in 1 above - this gives me the latest contact information.3. Finally I want to pull back the other fields (phone no, letter id etc) for the latest contact identified in 2 above.A colleague tried to give me a few pointers but she's up to her eyes and I hate pestering her! Any suggestions gratefully received!Thanks,R |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-07-26 : 19:39:21
|
If you are on SQL 2005 or higher, it is fairly simple to do using the row_number function:;with cte as( select *,ROW_NUMBER() OVER (PARTITION BY Customer_ID order by Contact_Date DESC,Contact_SeqNo DESC) RN from Customer_Contacts)select * from cte where RN = 1; |
 |
|
|
|
|
|
Using nested SELECTs on the same data source