MS SQL GROUP BY

Kinderly Wade
Kinderly Wade used Ask the Experts™
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'
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

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2011
Top Expert 2012
Commented:
select * from
(SELECT GUIDCustomer, GUIDInvoice, CreatedDate AS 'latestTransDate', row_number() over(partition by GUIDCustomer order by CreatedDate desc) as rn
FROM Invoice i
WHERE Completed = 1
AND Type = 'O'
) as x
where rn = 1
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

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
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial