Avatar of Kinderly Wade
Kinderly Wade
Flag for United States of America 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
DatabasesMicrosoft SQL ServerMongoDBSQL

Avatar of undefined
Last Comment
Mike Eghtebas

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Sean Stuber

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
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!
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
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