0

I have the following SQL query

    SELECT
    r.id as id,
    reports.Timestamp as reportTimestamp,
    reports.Latitude as reportLat,
    reports.Longitude as reportLng,
    FROM Requests r
    LEFT JOIN 

    (SELECT 
      c.* 
    FROM
      PositionReports c 
      LEFT JOIN PositionReports d 
        ON c.RequestId = d.RequestId 
        AND c.Timestamp < d.Timestamp 
    WHERE d.RequestId IS NULL) reports

    ON r.id = reports.RequestId 

So let's say the RequestsTable contains two entries, id's 178 and 179.

id otherdata
178 lorem....
179 ipsum....

They both have matching rows in the positionReports table.

The PositionReports table contains

id requestId applies Latitude Longitude Timestamp
1 178 0 56.5 11.9 012345678
2 179 1 57.2 12.2 012345800
3 179 1 57.0 12.0 012345900

Desired output: the query should retrieve both requests, and pick the positionReport with the highest value in Timestamp. If the applies field is false, the position report should be ignored.

requestId Timestamp Latitude Longitude
178
179 012345900 57.0 12.0

I have tried every combination I could think of to insert a WHERE applies = 1 but I always end up with either no position report data or a duplicate of that request...

Is there even a way to place such a condition in there?

1
  • Please provide SHOW CREATE TABLE for each table being used.
    – Rick James
    Jul 4, 2023 at 22:52

2 Answers 2

0

I tend to use partitions when selecting rows based on lastest time. The following achieves your desired output.

select requestId,
case applies when 1 then Timestamp else null end as Timestamp,
case applies when 1 then Latitude else null end as Latitude,
case applies when 1 then Longitude else null end as Longitude

from (
select id, requestId, applies, Latitude, Longitude, Timestamp,
max(Timestamp) over(partition by requestId) as latest
from PositionReports
)

where Timestamp = latest;

The subquery selects PositionReports as normal, but also includes a column which has the latest timestamp for the given requestId.

The outer query filters the records where the timestamp matches this lastest this latest timestamp, and uses case on applies to display as desired.

0

You can use a standard row-numbering solution.

SELECT
    r.id as id,
    reports.Timestamp as reportTimestamp,
    reports.Latitude as reportLat,
    reports.Longitude as reportLng,
FROM Requests r
LEFT JOIN (
    SELECT 
      reports.*,
      ROW_NUMBER() OVER (PARTITION BY reports.RequestId ORDER BY reports.Timestamp DESC) AS rn
    FROM
      PositionReports reports
    WHERE reports.applies = 1
) reports
   ON reports.RequestId = r.id
  AND reports.rn = 1;

It's unclear if you want to filter out applies before taking the first PositionReports or after. If after then move WHERE reports.applies = 1 to the outer level into the ON

   ON reports.RequestId = r.id
  AND reports.rn = 1
  AND reports.applies = 1;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.