CODEWITHSUNDEEP
sqlsql-servert-sql
Jack Reilly
Jack Reilly

Reputation: 168

How to join using values from a min() result row

I want to join two tables, without duplicates, using only the row with the most pressing deadline (min(date)) from the Sch_Milestone table.

I can join up the min(date) with my Sch_Schedule columns just fine, but when I try to attach the MTY_ID column from the same row as the min(date) I get duplicates again (it just adds all MTY_IDs rather than only the MTY_ID from the min(date) row).

How do I just join the MTY_ID from the row that has the min(date)? 

INSERT INTO @CurrentEmployeeSchedule
SELECT ss.ScheduleID
    ,ss.ResourceID
    ,ss.WorkReqID
    ,ss.SchDate
    ,ss.PriorityID
    --,sm.MTY_ID
    ,min(sm.MIL_DATE_TIME) AS 'Min Milestone'
FROM Sch_Schedule ss
LEFT JOIN Sch_Milestone sm ON sm.WR_ID = ss.WorkReqID
WHERE ss.ResourceID = @CurrentResourceID
    AND ss.SchDate = @TomorrowsDate
    AND sm.MTY_ID <> 10
GROUP BY ss.ScheduleID
    ,ss.ResourceID
    ,ss.WorkReqID
    ,ss.SchDate
    ,ss.PriorityID

So if I run the query above I don't get duplicates. But if I uncomment sm.MTY_ID i do. Because the second table has only 1 min(date) for each ScheduleID, but each ScheduleID matches up to mutiple MTY_IDs.

Commented: 

ScheduleID  ResourceID  WorkReqID   SchDate PriorityID  MIL_DATE_TIME
423604         241       13765  2015-08-05      5        2015-07-24 17:

Uncommented: 

ScheduleID  ResourceID  WorkReqID   SchDate PriorityID  MTY_ID  MIL_DATE_TIME
423604          241       13765     2015-08-05    5     40     2015-07-24 17
423604          241       13765     2015-08-05    5     50     2015-07-31 17

And what I want to get ( just the joined row on the min date but with MTY_ID) : 

ScheduleID  ResourceID  WorkReqID   SchDate PriorityID  MIL_DATE_TIME       MTY_ID
    423604         241       13765  2015-08-05      5        2015-07-24 17      40

Upvotes: 1

Views: 69

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93724

Use window function Row_Number to do this.

;WITH CTE
     AS (SELECT Row_number()
                  OVER(
                    PARTITION BY ss.ScheduleID, ss.ResourceID, ss.WorkReqID, ss.SchDate, ss.PriorityID
                    ORDER BY sm.MIL_DATE_TIME ASC) AS RN,
                ss.ScheduleID,
                ss.ResourceID,
                ss.WorkReqID,
                ss.SchDate,
                ss.PriorityID
                sm.MTY_ID,
                sm.MIL_DATE_TIME
         FROM   Sch_Schedule ss
                LEFT JOIN Sch_Milestone sm
                       ON sm.WR_ID = ss.WorkReqID
         WHERE  ss.ResourceID = @CurrentResourceID
                AND ss.SchDate = @TomorrowsDate
                AND sm.MTY_ID <> 10)
INSERT INTO @CurrentEmployeeSchedule
SELECT ResourceID,WorkReqID,SchDate,PriorityID,MTY_ID,MIL_DATE_TIME
FROM   CTE
WHERE  RN = 1

Upvotes: 2

Related Questions