SQL Query to get most latest record of ID's

[#1]

Hi All,

     I have a table which has ID,Name,Skill1,Skill2,Skill3,Mthyr as fileds in it.I need to get the max record of a ID.Lets supppose ID =1 has Mnthyr as 201209 as the latest record and ID =2 has 201211 as the latest record.In my resultant table i want only these records i meant latest records for the Id's.

Thanks in advance.

[#2]

Check the attachment with qvw and your excelfile. I think that's what you're looking for.

Hope it helps.

Short explanation:

- make a query in which you group by ID on max(date)

- join the fields you miss  due to the group by query

[manojkvrajan]

Lavanya, I assume MSID and Mnthyr combination identifies the unique record (as Key). Please see if the below mentioned script and attached samples helps.

Temp:
LOAD DISTINCT MSID, 
     max(Mnthyr) AS Mnthyr
FROM
Book1.xlsx
(ooxml, embedded labels, table is Sheet1)
GROUP BY  MSID;

TempKey:

LOAD *,
MSID&Mnthyr AS %Key
RESIDENT
Temp;

Book:
LOAD MSID&Mnthyr AS %Key,
*
FROM
[Book1.xlsx]
(ooxml, embedded labels, table is Sheet1)
WHERE EXISTS (%Key,MSID&Mnthyr);

DROP TABLES TempKey,Temp;
DROP FIELD %Key;

[#4]

Hi Manoj,

     Thanks for the reply I just added the filed Mnthyr but I need to get the Latest record of all th eMSId because of the skill info is not changed the same record will be maintained for a particular MSID.In that case  i need to look at the latest record of all the MSID's irrespective of

Please let me know if you need more info.

[#5]

Hi Manoj,

     Thanks for the reply I just added the filed Mnthyr but I need to get the Latest record of all th eMSId because of the skill info is not changed the same record will be maintained for a particular MSID.In that case  i need to look at the latest record of all the MSID's irrespective of

Please let me know if you need more info.

[daveamz]

Hi Lavanya,

     See if this code will do what you need:

maxOfID:

load max(Mnthyr) as MAXID, MSID

FROM

Book1.xlsx

(ooxml, embedded labels, table is Sheet1) Group by MSID;

data:

load MSID&'_'&MAXID as key Resident maxOfID;

left join (data)

LOAD

*,

MSID&'_'&Mnthyr as key

FROM

Book1.xlsx

(ooxml, embedded labels, table is Sheet1);

drop Table maxOfID;

Regards,

David

[manojkvrajan]

Lavanya, My understanding of the requirement is to display the skills of the MSID based on the latest Monthyear and ignore the rest. Please find below the quick snapshot. Please provide more information about the additional requirements on this sample data with an example