开发者

Getting the first or last result as part of a join

Currently I want to select the most recent value from a related table. So, I have a sale table which can have many transactions related to a single sale. I currently can use a subquery to fetch the most recent sale, as below, but it's very slow!

UPDATE Sales s
SET LastTrans = (SELECT Stamp 
                   FROM Transactions 
                  WHERE SalesID = s.ID 
               ORDER BY Desc LIMIT 1)
WHERE La开发者_运维知识库stTrans IS NULL;

Is there a way to do something like this using a join as if I do it for thousands of records it can take ages!


You didn't say what column your inner query was sorted by so I assumed it was Stamp.

UPDATE Sales s INNER JOIN (
    SELECT SalesID, MAX(Stamp) AS MaxStamp FROM Transactions
    GROUP BY SalesID
) AS t ON s.ID = t.SalesID
SET LastTrans = t.MaxStamp
WHERE LastTrans IS NULL;
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜