Foreach loop in the database SQL

Question

I have two tables in the database (Account and Transaction). I want to get the latest transaction of an account in the Transaction table. If I do it in, let's say PHP. It would be just get first all the accounts in Account table, stored in an array. Then select top 1 transaction from Transaction table (latest transaction base on date of the transaction) for each account by using foreach loop on the array.

My question is, is it possible to do the looping directly from the database? Foreach loop to be exact so I don't have to create an application made from any programming language such as PHP, Java etc just to get the data.

Thanks!

PS: I am using SQL Server 2000

Answer 1

This should get you the latest transaction information of all user...

SELECT t.*
From
transactions t1
INNER JOIN
(select max(t.transactionDate) transactionDate
, t.accountid
from transactions t
Group by t.accountid) t2
on ti.accountid=t2.accountid
and t1.transactionDate=t2.transactionDate
order by t1.transactionDate desc

Assumption: There is only one transaction associated with the max transactiondate per accountid