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 |
|
Jesse729
Starting Member
1 Post |
 Posted - 2011-03-29 : 16:12:59
|
| This is a basic question but it goes a little bit beyond the basics of the Min and Max functions Lets say you have 3 tables Transactions Customers and Products in Transactions you have Transaction PKID, Customer,PKID, ProductPKID and transactionDateI want to find the minimum or maximum transationdate for each customer but I want to list what the product purchased is. Basic Query would be Select Max(transaction.transactiondate) as maxdate, transactions.customerPKID from transactions group by customerPKID Results would give the highest purchase date and the customer PKID and I could inner join on the customer table to get any information about the customer. But How do I link back to the product table? If I group by productPKID as well if a customer brought 2 different products 2 results will be returned for each customer. I have gotten creative and linked back to the transactions table on transactiondate(Which is unique because it has the milliseconds included) and then linked to products But this is not ideal. |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-03-29 : 18:01:05
|
You could use a CTE to order the records and then pick the first one - something like this:with CTE as(select row_number() over (partition by t.customerPKID order by t.transactiondate desc) as RN, t.*, p.*from Transactions t inner join Products p on p.ProductPKId = t.ProductPKId)select * from CTE where RN = 1 |
 |
|
|
|
|
|
Min/Max with Dates and Joining