most recent payment query

Question

Post reply

most recent payment query

Ramona Roman

Old Hand

Points: 399
More actions
August 27, 2004 at 8:53 am

#161420

I have been tasked with querying one of the "payment" tables in our database to pull the most recent payment made to each account.
The table is arranged like this:
ID           Account #        Description        Date       Amt
1              123456           Gas Payment     5/8/04    20.56
2              123457          Deposit Payment 5/19/04 60.59
3              123456           Gas Payment      8/9/04    89.54
April 25, 2023 at 7:44 pm

This was removed by the editor as SPAM
April 28, 2024 at 1:19 pm

This was removed by the editor as SPAM
April 28, 2024 at 1:21 pm

This was removed by the editor as SPAM
April 28, 2024 at 1:25 pm

This was removed by the editor as SPAM

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

sadheesh kumar m Old Hand Points: 385 More actions · Answer 1

Hi,

select the most recent payment that time thorugh date u can select this

select * from payment where date between '2004-08-27' to '2004-08-20'

Ramona Roman Old Hand Points: 399 More actions · Answer 2

That option will not really work since last payment dates range from 2002-present. We have accounts that have not made payment in a LONG time.

Jamison Roberts SSC Enthusiast Points: 174 More actions · Answer 3

SELECT [ACCOUNT #], MAX([DATE]) FROM PAYMENT_TABLE GROUP BY [ACCOUNT #]

That would get you a result like the following for the above data:

123456 8/9/04

123457 5/19/04

If you need the Description, you can add that to both the select and group by list, however you'll get a record for each combination of account # and description.