This is my first question here. And I've been struggling for a while and I can't solve it.

So I want to get for all the circuits the location(latitude and longitude) to the most recent date.

The schema that I'm using contains important information so i will give an example:

table Circuit{
     NR int Primary Key,
     VEHICLE_ID int,
     etc...
}

table StopPoints{
     NR int Primary Key,
     Circuit int Foreign Key Circuit.NR,
     Latitude float,
     Longitude float,
     etc...
}

table StopPoint_Flux{
     NR int Primary Key,
     StopPoint int Foreign Key StopPoints.NR,
     Date DATE,
     etc...
}

And an example of the data in it:

Circuit:

StopPoints:

StopPoint_Flux:

The result that I need:

CodePudding user response：

If ROW_NUMBER works.

SELECT 
  q.VEHICLE_ID
, q.Latitude
, q.Longitude
, q.Date
FROM 
(
    SELECT 
      circuit.VEHICLE_ID
    , stop.Latitude
    , stop.Longitude
    , flux.Date
    , ROW_NUMBER() OVER (PARTITION BY circuit.NR
                         ORDER BY flux.Date DESC, stop.NR) AS rn
    FROM Circuit AS circuit
    JOIN StopPoints AS stop
      ON stop.circuit = circuit.NR
    JOIN StopPoint_Flux AS flux
      ON flux.StopPoint = stop.NR
) q
WHERE q.rn = 1

db<>fiddle here