Alex,

Try

SELECT Account, Amount
	FROM Transactions tr1
	WHERE Client_ID='123456' 
		AND Tran_Counter IN ( 
			SELECT MAX(Tran_Counter) FROM Transactions tr2
				WHERE tr2.Client_ID = tr1.Client_ID
					AND tr2.Account = tr1.Account)

>Brain is slow today
>
>I have a SQL Server 2000 table with about 12 million records. These are individual transactions.
>The way they work is each record has the last accummulated balance. Why? Because they refer to a smart card that has an embedded chip. The chip records the transaction number and the current balance.
>
>I just got asked for a report on a certain group of cards (same Client_ID). They want to get the current balance of each card. We do not have such a report on the app they use as it was never required. We do have a program that queries one card and lists all their transactions, calculating the current balance. They do not want that this time. There is always a difference between calculated and chip balance as some transactions may not have been yet received here (e.g. a POS terminal has not 'closed' yet).
>
>Task: I need to query SQL Server for the balance in the last transaction of each account. For simplicity say I have the following fields (actually it's much more complex and sample field names are not the real ones but will do for this example):
>

>Transactions table:
>Client_ID C(6)
>Account C(16)
>Tran_Counter N(5)
>Amount N(10,2)
>

>So I need to get the Amount for the MAX(Tran_Counter) for a client. I need ALL individual Accounts (i.e. no GROUP BY). Syntax should be T-SQL.
>

>SELECT Account, Amount
>FROM Transactions
>WHERE Client_ID='123456'
>...
>...
>

>I need it for MAX(Tran_Counter), so I think it must be a correlated query? But I can;t seem to get it right.
>
>TIA