Sorting by a field in child table There are two tables: contract (with primary key contractid) and contractupdate (with field contractid referring to a contract).

contractupdate also has DATE field startdate and TINYINT field day.

I want the list of contract IDs ordered by the value of field day in the latest (having maximal startdate) contract update which refers to this contract.

MySQL.
SELECT B.contractid,B.startdate,B.day
FROM
    (SELECT MAX(startdate) startdate,contractid
    FROM contractupdate GROUP BY contractid) A
    INNER JOIN
    contractupdate B USING (contractid,startdate)
;