SQL Order By in Left Join Select

Question

I have a system that contains two tables. A "projects" table, and an "additions" table. The projects table contains original data for a "project". The details on the project cannot be changed directly. Instead an "addition" is used if any details need to change. This way the original entry in projects is maintained and as additions are added all the changes can be tracked in order.

One of the project fields is "projectEndDate" which contains the end date of the project. This can be overriden with an addition with "additionEndDate". Additions also contains "additionDateEntered" which logs when the addition was actually entered into the system.

I need to be able to grab all the projects while left joining the additions that contain a new endDate ordered by the newest "additionDateEntered" and getting the "additionEndDate" for that entry. And to finalize, the entire results should be ordered by the "realClosingDate" which is an alias of the newest end date. Usually I can work through these queries but I seem to be really struggling with this one.

Here's what I have so far

SELECT 
    *,
    `additionEndDate`,
    GREATEST(COALESCE(`projectEndDate`, 0),
            COALESCE(`additionEndDate`, 0)) AS `realEndDate`
FROM
    (SELECT 
        *
    FROM
        `projects`
    WHERE
        `projectStatus` = ‘Active’) AS A
        LEFT JOIN
    (SELECT 
        *
    FROM
        `additions`
    WHERE
        `additionEndDate` IS NOT NULL
    GROUP BY `projects_projectId`
    ORDER BY `additionEnteredDate` DESC) AS B ON A.projectId = B.projects_projectId
ORDER BY `realEndDate`

The result does not get the latest additionEnteredDate but instead seems to get the first entry in additions only.

EDIT: Here is a sample project and 4 sample additions tied to that project

The query above results in realClosingDate of 2020-02-12 however the require result should be 2020-02-22 as that is the latest entered addition but not necessarily the MAX() of additionEndDate

@GordonLinoff thanks for the suggestion, I have added samples and clarification on desired results — Badams, Feb 20 '20 at 22:47

Gordon Linoff · Accepted Answer · 2020-02-21T19:40:11.023

1

This answers the original question.

SELECT * with GROUP BY!!! That is just a bug. And the most recent versions of MySQL don't even allow it.

It does at least make the logic easy to fix:

SELECT p.*, a.max_additionEndDate,
       GREATEST(COALESCE(p.projectEndDate, a.max_additionEndDate),
                COALESCE(a.max_additionEndDate, p.projectEndDate)
               ) as realEndDate
FROM projects p LEFT JOIN
     (SELECT a.projects_projectId, MAX(additionEndDate) as max_additionEndDate
      FROM additions` a
      GROUP BY projects_projectId
     ) a
     ON a.projectId = p.projects_projectId
WHERE p.projectStatus = 'Active'
ORDER BY `realEndDate`

edited Feb 21 '20 at 19:40

answered Feb 20 '20 at 22:51

Gordon Linoff

1,242,037
58
646
786

This worked flawless in the sample data, however I do have a question... by using MAX(additionEndDate) wont this always select the latest EndDate? This may not work as there may be a scenario where a second addition in fact changes to a sooner date. In sample data, imagine an addition with a EnteredDate of 2020-02-20 with an EndDate of 2020-02-23 – Badams Feb 20 '20 at 23:07
I've added an additional sample to the additions sample data to demonstrate my example – Badams Feb 20 '20 at 23:17
@Badams . . . It is better to ask a new question, once a question has been answered. – Gordon Linoff Feb 21 '20 at 00:03
while your answer did work for the original sample data, it did not work following all scenarios described, specifically 'I need to be able to grab all the projects while left joining the additions that contain a new endDate ordered by the newest "additionDateEntered" and getting the "additionEndDate" for that entry'. Grabbing the max() EndDate doesnt work if the EndDate becomes sooner in the most recent addition. I don't even know the status of this question anymore as someone closed it claiming it was a duplicate (which it wasn't) and now its open again. – Badams Feb 21 '20 at 17:02

SQL Order By in Left Join Select

1 Answers1