Load first 2 records in a group

[#1]

Hi,

anyone have any idea how to load only the first 2 records according to the latest birthday of every group?

Expected Result:

For Group 1, Alice,Jon

For Group 2, David,Derrick

For Group 3, Serene,Rachel

Dataset:

Name	Birthday	Group
Alice	01/01/87	1
David	02/03/87	2
Jason	29/04/87	3
Jon	01/04/87	1
Derrick	03/05/87	2
Aaron	17/07/87	2
Jerome	20/09/87	1
Serene	23/02/87	3
Rachel	21/04/87	3

Thank you.

[kumarnatarajan]

Hi,

Load below script.

LOAD * Where WildMatch(AutoNumber(Group&RowNo(),Group),1,2);

LOAD * INLINE [

    Name, Birthday, Group

    Alice, 01/01/87, 1

    David, 02/03/87, 2

    Jason, 29/04/87, 3

    Jon, 01/04/87, 1

    Derrick, 03/05/87, 2

    Aaron, 17/07/87, 2

    Jerome, 20/09/87, 1

    Serene, 23/02/87, 3

    Rachel, 21/04/87, 3

];

I think For Group 3, Jason,Serene

[#3]

Group 3 should be Serene and Rachel because I will order by the birthday and get the first 2 records of it.

[kumarnatarajan]

Ok No prob. you can get result after using order by.

try below one.

Temp:

LOAD * INLINE [

    Name, Birthday, Group

    Alice, 01/01/87, 1

    David, 02/03/87, 2

    Jason, 29/04/87, 3

    Jon, 01/04/87, 1

    Derrick, 03/05/87, 2

    Aaron, 17/07/87, 2

    Jerome, 20/09/87, 1

    Serene, 23/02/87, 3

    Rachel, 21/04/87, 3

];

NoConcatenate

Temp2:

LOAD * Resident Temp Where WildMatch(AutoNumber(Group&RowNo(),Group),1,2) Order by Birthday;

DROP Table Temp;

[SunilChauhan]

see the attached file

hope this helps