How can I get last and second balance from the table below.

[north2060]

CNo Date Balance
1 02/11 3000
2 03/11 400
1 05/11 2800
2 05/11 350
1 09/11 2600
1 11/11 2000
2 11/11 300

CNO is Account number.

Thank you!

 

[PHV]

What is the expected result with your sample data ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[north2060]

There will be 2 sql queries to find out

Second last transaction:
1 09/11 2600
2 05/11 350

Last transaction:
1 11/11 2000
2 11/11 300

Thank you!

 

[PHV]

Last transaction:
SELECT A.CNo, A.Date, A.Balance
FROM yourTable AS A INNER JOIN (
SELECT CNo, Max([Date]) AS LastDate FROM yourTable GROUP BY CNo
) AS L ON A.CNo = L.CNo AND A.Date = L.LastDate

Second last transaction:
SELECT A.CNo, A.Date, A.Balance
FROM yourTable AS A INNER JOIN (
SELECT B.CNo, Min(B.Date) AS theDate FROM yourTable AS B WHERE B.Date In
(SELECT TOP 2 [Date] FROM yourTable WHERE CNo=B.CNo ORDER BY 1 DESC)
GROUP BY B.CNo
) AS L ON A.CNo = L.CNo AND A.Date = L.theDate

Last and Second last transaction:
SELECT A.CNo, A.Date, A.Balance
FROM yourTable AS A
WHERE A.Date In (SELECT TOP 2 [Date] FROM yourTable WHERE CNo=A.CNo ORDER BY 1 DESC)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886