How can I fetch only the latest records?

[#1]

Hi Everyone,

I have following fields in the table, with the single or multiple records for each of the ID.

I want latest record of each of the ID in a seperate table at script level, like as below:

I dont know whether it is possible or not, but any help would be greatly appreciated. Please guide me on this. Best Regards

[#2]

Hi Niks,

             Please find attached Example application with some modification in Script,

Hope it will helpful for you.

All the Best.

Regards,

Antony.

[#3]

Hi Sunil,

            I used below code, it  is working in my application.

Max_Value:

LOAD ID,

     Name,

     [Registration Date],

     [Expiry Date],

     [License Type]

FROM

C:\Users\lenovo\Desktop\Max_Value.xls

(biff, embedded labels, table is Sheet1$);

Test:

join load ID,

max([Registration Date]) as Max_Reg_Date

Resident Max_Value

Group By  ID

;

Test1:

load *,

1 as Junk

Resident Max_Value

Where [Registration Date] = Max_Reg_Date

;

DROP Tables  Max_Value;

[SunilChauhan]

ur excel entry shwing data of 2012  so plz try this....

Max_Value:
LOAD ID,
     Name,
     [Registration Date],
     [Expiry Date],
     [License Type]
FROM
C:\Users\lenovo\Desktop\Max_Value.xls
(biff, embedded labels, table is Sheet1$);

Test:
load ID,
[Registration Date],
Name,

   [Expiry Date],

     [License Type],
max([Registration Date]) as Max_Reg_Date
Resident Max_Value

Group By ID,[Registration Date],Name,

[Expiry Date],

     [License Type]

;

Test1:
Load ID,
MAX(Max_Reg_Date) AS Max_Reg_Date

resident Test where [Registration Date]=Max_Reg_Date;

group by  ID
drop tables test,Max_Value;

[#5]

Hi Sunil,

         It showing in Expriry Date  field only, but we need  the max date from Registration Date not from Expiry Date.

[#6]

Sunil, It worked perfectly fine. Thanks a lot. Appreciated.

[#7]

Antony, Thanks for the wonderful solution. Appreciated. Best Regards.

[#8]

Hi Niks,

            Thank you...