Find Max dates/times group by position (1 Viewer)

[boerbende]

Dear Query experts,

In the below example I am able to get the correct answer, but I have the feeling my method is too complex

A very simplified example is the results of a UNION query (records are coming from different tables)

Query1
Position ; DateTime ; Value
242 ; 26-12-2019 10:00 ; 1
241 ; 25-12-2019 07:00 ; 2
243 ; 26-12-2019 07:00 ; 3
242 ; 26-12-2019 07:00 ; 6
242 ; 26-12-2019 04:45 ; 2
241 ; 26-12-2019 11:00 ; 8
243 ; 25-12-2019 07:00 ; 4

The results should be the Max from datetime group by position
241 ; 26-12-2019 11:00 ; 8
242 ; 26-12-2019 10:00 ; 1
243 ; 26-12-2019 07:00 ; 3

I do this by a second query SELECT max(datetime) from "query1" group by position and link the result to query1 (pos to pos and datetime to datetime)

Are there methods to simplify this?

Thanks in advance.

Ben

 

[June7]

If you want the entire record, that's probably as simple as any other method. Review this TOP N example: http://allenbrowne.com/subquery-01.html#TopN

 

[boerbende]

Thanks!
It is indeed the top N per group. This is actually what I use to create the first query, and on the result (query1) I just have to perform it again