Selecting only the last record from database

[#1]

Hi,

Suppose I have the following :

ITEMNO	DATE	QTY
001	10/10/11	10
001	11/10/11	20
001	12/10/11	30
001	13/10/11	40
001	14/10/11	50
002	12/10/11	10
002	13/10/11	20
002	14/10/11	30
003	14/10/11	30
003	14/10/11	50

How can I select only the last transactions for each ITEMNO? Below should be the selected rows.

001

14/10/11

50

002

14/10/11

30

003

14/10/11

50

Regards,

Marson

[suniljain · Accepted solution]

Pls Check Following logic.

Load  

        ItemNo,

        Max(Date) as Date, 

        Sum(Qty) as Qty

from XYZ Group By ItemNo

[#3]

Hi,

I am also having a same kind of problem.

I have two tables One is having Month wise Net sales (LCY) and second table contains the month wise exchange rates. Now I want the third table contains the Net Sales figure / Month's exchange rate (say 10000 / 68.84027).

Upto this I don't have any problem, but when current month's excahnge rate is missing then I want my current month's Sales data should get treatment with the last month's exchange rate or last record of the exchange rate table.

[Net Sales]  / IF ([Year-Month] = '2012-01' , [Exchange Rate], if([Year-Month] = '2011-12', [Exchange Rate] ,0))

In my case IF condition which is in BOLD is not working.

Thanks in advance.