Link to home
Start Free TrialLog in
Avatar of Kinderly Wade
Kinderly WadeFlag for United States of America

asked on


Dear experts,

I have a query that look like this:

SELECT GUIDCustomer, MAX(CreatedDate) AS 'latestTransDate'
FROM Invoice i
WHERE Completed = 1
AND Type = 'O'

The result will give me the latest date of each customer in Invoice table when invoices are completed.

I also wish to obtain the GUIDInvoice from the above query but unfortunately, I need to add GUIDInvoice in GROUP BY clause.
What I want to achieve is to find the latest transaction date by each customer and also obtain the GUIDInvoice of the invoice with latest transaction date. I wish to know how can I adjust my query to obtain that result. Thanks
Avatar of Sean Stuber
Sean Stuber

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
(Select GUIDCustomer, GUIDInvoice, CreatedDate AS 'latestTransDate' 
row_number() OVER ( PARTITION BY GUIDCustomer, Order By CreatedDate Desc) AS rn
FROM Invoice
WHERE Completed = 1
AND Type = 'O') d
Where rn=1

Open in new window

Avatar of Sean Stuber
Sean Stuber

Isn't 42071988 just a repeat of the first post except with a couple of syntax errors?

 there should be no comma between the partition clause and the order by clause of the windowing parameter
and there should be a comma before the row_number call
Avatar of Kinderly Wade


Thanks sdstuber!
Thanks sdstuber!

Yes, you are right. I have an extra coma in my code. After I submitted my post, I saw your solution. By the time I wanted to delete my post, you already had added a new post thus blocking me to delete it.

As you may know, the logo of expert exchange is the same as car-race flag meaning the first correct answer gets the points. So, it was understood that there was no worries for you to lose any points.