Latest Record Based on Timestamp

[sunitha_chellaiah]

Hi,

I need to fetch the latest record from table based on Updated Date and Updated Time field

Note: Needs to do it back end script

Shift	Values	Sr. No	Updated Date	Updated Time
1	0.43	1000	22-10-2017	15:44:08
1	0.36	2000	22-10-2017	21:10:39
1	0.48	3000	22-10-2017	21:10:38
2	0.43	4000	22-10-2017	21:13:05
3	0.43	5000	22-10-2017	21:12:16
1	0.12	6000	23-11-2017	21:12:15
2	0.12	7000	23-11-2017	21:12:14
2	0.13	8000	23-11-2017	15:05:38
3	0.12	9000	23-11-2017	15:05:37

Only latest time should appear against each shift.

output should come as:

Shift	Values	Sr. No	Updated Date	Updated Time
1	0.36	2000	22-10-2017	21:10:39
2	0.43	4000	22-10-2017	21:13:05
3	0.43	5000	22-10-2017	21:12:16
1	0.12	6000	23-11-2017	21:12:15
2	0.12	7000	23-11-2017	21:12:14
3	0.12	9000	23-11-2017	15:05:37

[sunny_talwar · Accepted solution]

Try this

Table:

LOAD * INLINE [

    Shift, Values, Sr. No, Updated Date, Updated Time

    1, 0.43, 1000, 22-10-2017, 15:44:08

    1, 0.36, 2000, 22-10-2017, 21:10:39

    1, 0.48, 3000, 22-10-2017, 21:10:38

    2, 0.43, 4000, 22-10-2017, 21:13:05

    3, 0.43, 5000, 22-10-2017, 21:12:16

    1, 0.12, 6000, 23-11-2017, 21:12:15

    2, 0.12, 7000, 23-11-2017, 21:12:14

    2, 0.13, 8000, 23-11-2017, 15:05:38

    3, 0.12, 9000, 23-11-2017, 15:05:37

];

Right Join (Table)

LOAD Shift,

[Updated Date],

Max([Updated Time]) as [Updated Time]

Resident Table

Group By Shift, [Updated Date];

[sunitha_chellaiah]

Thank you so much!  Sunny