尝试使用MAX(date)在查询中查找TOP时出现问题

我在弄清楚如何从查询中获得正确的日期时遇到了问题。我需要为每个工单编号提取最早的日期，但由于我的子查询中不能有Order By，所以我不确定该走哪条路线。--MSSQL 2016

Create Table #FOO
(
    Job varchar(4),
    CurrentMilestone varchar(10),
    MilestoneDate DATETIME
)

INSERT INTO #FOO (Job,CurrentMilestone,MilestoneDate)
    VALUES ('P5','ONSITE','2017-10-23');
INSERT INTO #FOO (Job,CurrentMilestone,MilestoneDate)
    VALUES ('P5','PAINT','2017-11-01');
INSERT INTO #FOO (Job,CurrentMilestone,MilestoneDate)
    VALUES ('P5','SHIP','2018-02-01');
INSERT INTO #FOO (Job,CurrentMilestone,MilestoneDate)
    VALUES ('P5','OFFSITE','2017-12-20');
INSERT INTO #FOO (Job,CurrentMilestone,MilestoneDate)
    VALUES ('P16','BREAK','2017-10-10');
INSERT INTO #FOO (Job,CurrentMilestone,MilestoneDate)
    VALUES ('P16','ONSITE','2017-11-11');
INSERT INTO #FOO (Job,CurrentMilestone,MilestoneDate)
    VALUES ('P16','HACK','2017-12-20');
INSERT INTO #FOO (Job,CurrentMilestone,MilestoneDate)
    VALUES ('P16','SHIP','2018-05-01');

SELECT * FROM #FOO;  

 SELECT f.Job,f.CurrentMilestone, f.MilestoneDate
    FROM (SELECT Job, MAX(MilestoneDate) as MilestoneDate 
        FROM #FOO
        GROUP BY Job
            ) a
 JOIN #FOO f ON a.Job = f.job AND a.MilestoneDate = f.MilestoneDate
 ORDER BY f.MilestoneDate ASC
        IF OBJECT_ID('tempdb..#FOO') IS NOT NULL DROP TABLE #FOO
GO

我的返回应该是这样的：

Job     CurrentMilestone     MilestoneDate
P5      ONSITE               2017-10-23
P16     BREAK                2017-10-10

转载请注明出处:http://www.beijingklxcmc.com/article/20230526/2243127.html

➤