asked on

MS SQL GROUP BY

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'
GROUP BY GUIDCustomer

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

ASKER CERTIFIED SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mike Eghtebas

SELECT * From
(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

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

Kinderly Wade

ASKER

Thanks sdstuber!

Kinderly Wade

ASKER

Thanks sdstuber!

Mike Eghtebas

@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.

Regards,

Mike