getting latest record according to date from table

[berryandcherry6]

Hi,

i have a table with multiple lender_id for single user_id.

lender_id    user_id     date_created

43	1	12/19/2016
44	1	12/20/2016
45	1	12/21/2016
46	2	12/19/2016
47	2	12/25/2016
48	2	12/23/2016
49	2	12/20/2016
50	3	12/20/2016
51	3	12/21/2016
52	1	12/25/2016
53	1	12/21/2016
54	4	12/19/2016

Now i need to put query to get as this  

lender_id    user_id     date_created

47	2	12/25/2016
51	3	12/21/2016
52	1	12/25/2016
54	4	12/19/2016

How could i do this. Please help me.

[sunny_talwar]

Did you try this?

Re: Error: Function WeekStart Doesnot Exist. In Where clause

[Anonymous · Accepted solution]

Hi,

Data:

LOAD * inline [

lender_id,    user_id,     date_created,

43, 1, 12/19/2016

44, 1, 12/20/2016

45, 1, 12/21/2016

46, 2, 12/19/2016

47, 2, 12/25/2016

48, 2, 12/23/2016

49, 2, 12/20/2016

50, 3, 12/20/2016

51, 3, 12/21/2016

52, 1, 12/25/2016

53, 1, 12/21/2016

54, 4, 12/19/2016 ];

NoConcatenate

Data_TEMP:

LOAD lender_id,

     user_id,

     date#(date_created,'MM/DD/YYYY') as date_created

Resident Data;

drop table  Data; 

Data_Max:

load user_id, max(date_created) as date_created

Resident Data_TEMP

Group by user_id ;

left join(Data_Max)

LOAD lender_id,

     user_id,

     date_created

Resident Data_TEMP;

drop table  Data_TEMP; 

Best regards,

Cosmina