We help IT Professionals succeed at work.

MS SQL GROUP BY

292 Views
Last Modified: 2017-04-06
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
Comment
Watch Question

Database Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
Mike EghtebasDatabase and Application Developer

Commented:
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 StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
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 Wadeprogrammer

Author

Commented:
Thanks sdstuber!
Kinderly Wadeprogrammer

Author

Commented:
Thanks sdstuber!
Mike EghtebasDatabase and Application Developer

Commented:
@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