Question 200619 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – How to SQL select only most recent from related table

I have a table of requests from which I want to select everything, and I have a related table of positionReports where I want to select only the most recent report.

The below SQL does what I want as long as there is only one position report, but as soon as there are more, I understand that the SQL has no idéa which one I want. But I don’t know how to specify that.

SELECT
r.id mission_id,
report.Timestamp as reportTimestamp,
report.Latitude as reportLat,
report.Longitude as reportLng,
FROM Requests r
LEFT JOIN PositionReports report ON r.id = report.RequestId

—————- UPDATE ————-

Guided by this MySQL JOIN the most recent row only? I managed to fetch the most recent one, but then a new problem is that I only get retrieved such missions that actually has position reports.

    WHERE report.Id = (
        SELECT MAX(Id)
        FROM PositionReports
        WHERE RequestId = r.Id
    )";

Is there a way to retrieve all such that don’t have an entry in the Reports table as well? Tried outer joins but no difference…

It is a MySQL database (testing environment is MariaDB but production is MySQL (DigitalOcean).

When you suggest pre-aggregating, does that mean that what I aim to accomplish is not possible in 1 SQL query alone?

Sample data ———————–

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

id otherdata
178 lorem….
179 ipsum….

Only one of these, 179, has matching rows in the positionReports table.

The PositionReports table contains

id requestId Timestamp Latitude Longitude
2 179 123456700 56.5 11.9
1 179 123456789 57.0 12.0

Desired output (the query should retrieve both). Ideally pick the positionReport with the highest value in Timestamp, but sorting on Id would work too for this implementation as I force chronology in accepting the reports.

requestId Timestamp Latitude Longitude
178
179 123456789 57.0 12.0
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I think I made it at least work thanks to this Return all data from 1st Table, and only 1 data from 2nd Table if exist

    I'm not sure how efficient (or should I say resource-hogging) this solution is.

            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

  2. 0

    With no claims about resource consumption – an alternative approach, using the RANK window function:

    SELECT
  r.id as id,
  reports.Timestamp as reportTimestamp,
  reports.Latitude as reportLat,
  reports.Longitude as reportLng
FROM RequestsTable r
LEFT JOIN (
  SELECT
    *,
    RANK() OVER (PARTITION BY RequestId ORDER BY Timestamp DESC) as rnk
  FROM PositionReports
  ) reports
  on reports.RequestId = r.id
  and reports.rnk = 1
;

    See it in action: DB Fiddle.

    Please comment, if and as this requires adjustment / further detail.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search