• Status: Solved
  • Priority: High
  • Security: Public
  • Views: 46
  • Last Modified:

SQL Query for Max Date

I got this SQL query that I'm running against a table to pick the latest date an item was sold (PurchaseDate), pick the customer that bought it along with other info, the thing is, an item can be returned by one customer and bought by another, so If I'm running Max date function and grouping on Customer, I'll get both customers, the one that returned the item and the one that bought it, and it's critical to bring the customer out in the query. I need to select the item (Unit_Serial_no) by the last date it was sold (PurchaseDate) by that customer, and ignore past customers that had that item as some point. Please advise.
Regards
Table_Example
0
Roberto Madro R.
Asked:
Roberto Madro R.
  • 2
  • 2
1 Solution
 
awking00Commented:
select customer, unit_serial_no, model, purchaseDate from
(select customer, unit_serial_no, model, purchaseDate,
 row_number() over (partition by unit_serial_no, model order by purchasedate desc) rn
 from yourtable)
where rn = 1;
0
 
Roberto Madro R.Programmer AnalystAuthor Commented:
Thank you awking00, just what the doctor ordered.
0
 
Roberto Madro R.Programmer AnalystAuthor Commented:
Many thanks
0
 
awking00Commented:
You're welcome - glad to help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now