Perform a join on a filtered table
Posted on 2008-02-08
This question is related to another open question I have in the SQL section.
Can someone let me know if the following is possible? Keep in mind that my base table (Table1) contains millions of records, so I'm looking for the most efficient way to achieve this.
To simplify things, let's say Table1 contains 3 fields. They are CustomerNumber, TransactionDate, and Comments. Numerous occurences of the same CustomerNumber can appear in the table. The data in Table1 might look like this:
CustomerNumber TransactionDate Comments
111 1/1/1999 This is an older comment for 111
111 12/31/2007 This is the newest comment for 111
222 6/30/2004 This is an older comment for 222
222 10/1/2006 This is a newer comment for 222
222 1/1/2008 This is the newest comment for 222
What I need:
I need the most up-to-date record for each CustomerNumber so that I'll wind up with unique occurences of each CustomerNumber. The reason I need this is because I will be joining on CustomerNumber from another table. People have already shown me how to get the most up-to-date record (by using MAX(TransactionDate), etc from Table1. Originally what I was going to do was get the most up-to-date record for each CustomerNumber in Table1 and dump them into a new table. Do I need to do this though? Or can I somehow run a query on Table1 to get the the unique CustomerNumbers (by selecting the most up to date record for each only) I'm talking about and then join my other table (which also contains a CustomerNumber field) on CustomerNumber of the 'results table/view' from the query I ran on Table1?