title: SQL Server INNER JOIN with GROUP BY
body: <p>I have two tables that I want to join. The first table is named <code>WorkItem</code>:</p>
  
  <p><img src="https://i.stack.imgur.com/zC4in.png" alt="enter image description here"></p>
  
  <p>The second table is <code>WorkItem_Schedule</code>:</p>
  
  <p><img src="https://i.stack.imgur.com/9pAce.png" alt="enter image description here"></p>
  
  <p>I want to get the <strong>1st</strong> joined row with <code>ActualEndDate IS NULL</code>.</p>
  
  <p>In case of WorkItemId 3, I only want to join it on WorkItemScheduleId 95. Only the first row! </p>
  
  <p>I tried this SQL statement, but I am having trouble: </p>
  
  <pre><code>  SELECT 
        W.WorkItemId
        ,MIN(WS.WorkItemScheduleId) test
        ,W.WorkItemName
        ,WS.[PhaseName]
        ,WS.[StartDate]
        ,WS.[EndDate]
        ,WS.[ActualStartDate]
        ,WS.[ActualEndDate]
    FROM 
        WorkItem W
    INNER JOIN 
        WorkItem_Schedule WS ON W.WorkItemId = WS.WorkItemId
    WHERE 
        WS.ActualEndDate IS NULL
    GROUP BY 
        W.WorkItemId;
  </code></pre>
  
  <p>I get an error </p>
  
  <blockquote>
    <p>Column 'WorkItem.WorkItemName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.</p>
  </blockquote>
  
  <p>I google this error and found out that I need to add other columns in the select list to group by, so I tried this sql, but it return all schedules and not distinct workitemid: </p>
  
  <pre><code>SELECT 
     W.WorkItemId
    ,MIN(WS.WorkItemScheduleId) test
    ,W.WorkItemName
    ,WS.[PhaseName]
    ,WS.[StartDate]
    ,WS.[EndDate]
    ,WS.[ActualStartDate]
    ,WS.[ActualEndDate]
  FROM 
      WorkItem W
  INNER JOIN 
      WorkItem_Schedule WS ON W.WorkItemId = WS.WorkItemId
  WHERE 
      WS.ActualEndDate IS NULL
  GROUP BY 
      W.WorkItemId, WS.PhaseName, WS.StartDate, WS.EndDate, WS.ActualStartDate, WS.ActualEndDate;
  </code></pre>
  
  <p>Please help! THanks in advance! </p>