SQL Syntax

healthcheckinc
healthcheckinc used Ask the Experts™
on
Stupid question, but I need the answer quickly so thats why Im posting it. I want to return one account record with the max completed date. I would normally have a couple of records with the same account_id but only want the max date returned. When I do a simple statment for 1 account it works but I want all the accounts with only the max date being returned for each. Example

Select max(completed), account_id, current_balance_amount
from payments p
Inner Join payment_request_accounts pra on p.id = pra.payment_request_id
Where pva.account_id = 7104739
group by account_id, current_balance_amount

Problem is when I add multiple accounts it only gives me the Max completed date of all the accounts. I need it for each accountID

Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
AneeshDatabase Consultant
Top Expert 2009

Commented:
assuming that Completed is on the payment_request_accounts

Select max(completed), account_id, current_balance_amount
from payments p
Inner Join payment_request_accounts pra on p.id = pra.payment_request_id
Where Completed = (SELECT MAX(Completed) FROM payment_request_accounts  pr WHERE  p.id = pr.payment_request_id  )
SQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018
Commented:
Hope this is what you want..

SELECT completed, account_id, current_balance_amount
FROM (
Select completed, account_id, current_balance_amount, row_number() over ( partition by account_id order by completed desc) rnum
from payments p
Inner Join payment_request_accounts pra on p.id = pra.payment_request_id
Where pva.account_id = 7104739) temp
WHERE rnum = 1

Open in new window

Author

Commented:
sorry but neither worked
HTML5 and CSS3 Fundamentals

Build a website from the ground up by first learning the fundamentals of HTML5 and CSS3, the two popular programming languages used to present content online. HTML deals with fonts, colors, graphics, and hyperlinks, while CSS describes how HTML elements are to be displayed.

Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
Kindly provide the result set which the query generated along with the expected result set to help you better..

Author

Commented:
Actually I have to test this again...Will get back to you soon...Sorry for the confusion and delay
Hi healthcheckinc

Give this a try:

Select max(completed), account_id, current_balance_amount
from payments p
Inner Join payment_request_accounts pra on p.id = pra.payment_request_id
Where pva.account_id IN (SELECT Accounts.account_id FROM Accounts WHERE <other conditions> )
group by account_id, current_balance_amount
 
Hope this helps

Author

Commented:
your right it worked...Thanks
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
Welcome..

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