Show Recent Date and Group By ID

[metin_inksen]

Hi everyone,

I've got the following table:

id	expedition-id	vehicle-id	time	latitude	longitude
1	400	1	11/4/21 09.00	41.1	28.0
2	400	1	11/4/21 12.00	41.0	28.0
3	401	2	11/5/21 08.30	41.4	28.4
5	401	2	11/5/21 14.00	41.3	28.2
6	402	3	11/6/21 09.30	41.8	28.7
7	402	3	11/6/21 14.30	41.7	28.6
8	403	4	11/7/21 09.00	41.5	28.9

What I'm trying to do is group by expedition-id and show the most recent date.

id	expedition-id	vehicle-id	time	latitude	longitude
1	400	1	11/4/21 12.00	x	y
2	401	2	11/5/21 14.00	x	y
3	402	3	11/6/21 14.30	x	y
4	403	4	11/7/21 09.00	x	y

My goal is get the recent location of shipping vehicles. How can I do this? Thanks in advance.

[rubenmarin · Accepted solution]

Hi you can do a group by by the field that doesn't changes, and use an aggretion expression for the other fields, in this case I don't know what you want to do with latitude and longitude, wich can have different values for each expedition-id.

To retunr the most recent date you can use max(), this returns a number, with Timstam() you can give a timestamp format.

There is also the option to use FirstValue as aggregation function, to retrieve the first loaded value.

In summary it could be something like:

LOAD
  expedition-id,
  FirstValue(id) as id,
  FirstValue(vehicle-id) as vehicle-id,
  FirstValue(latitude) as latitude,
  FirstValue(longitude) as longitude,
  Timestamp(Max(time), 'M/D/YY hh.mm') as time
Resident/From...
Group by  expedition-id;