title: SQL Get First Value From Table
body: <p>I'm running MS SQL-Server-2016 I'm trying to get the first value from a table partitioned an ParentId.  I have a data set like so:</p>
  
  <pre><code>CREATE TABLE #TempTable
  (
      PTORequestDetailId INT,
      PTORequestId INT,
      DivisionPTOAccrualId INT,
      RequestDate DATE,
      StartTime DateTime,
      Hours Decimal(19,2),
      ResponseNotes nvarchar(500),
      Status int,
      CreatedOn DateTime,
      ModifiedOn DateTime
  )
  
  INSERT INTO #TempTable
  SELECT 60,  48, 139, '2015-09-09',  '2015-09-08 20:00:00.000',  8.00,'',0, '2015-09-09 13:30:40.363',   '2015-09-09 13:30:40.313' UNION
  SELECT 61,  48, 139, '2015-09-10',  '2015-09-09 20:00:00.000',  8.00,'',0, '2015-09-09 13:30:40.363',   '2015-09-09 13:30:40.313' UNION
  SELECT 62,  48, 139, '2015-09-11',  '2015-09-10 20:00:00.000',  8.00,'',0, '2015-09-09 13:30:40.363',   '2015-09-09 13:30:40.313' UNION
  SELECT 63,  48, 139, '2015-09-13',  '2015-09-12 20:00:00.000',  8.00,'',0, '2015-09-09 13:30:40.363',   '2015-09-09 13:30:40.313' UNION
  
  CREATE TABLE #TempParent
  (
      PTORequestId INT,
  )
  
  INSERT INTO #TempParent
  SELECT 48
  </code></pre>
  
  <p>Running this will create a temp table with the data set I'm working with.
  I'm trying to get out the First Value for the DivisionPTOAccrualId partitioned by the #TempTable.PTORequestId,</p>
  
  <pre><code>SELECT #TempParent.*, AccrualDetails.*
  FROM #TempParent
  CROSS APPLY (
          SELECT FIRST_VALUE(#TempTable.DivisionPTOAccrualId) 
              OVER (PARTITION BY #TempTable.PTORequestId 
                      ORDER BY #TempTable.PTORequestDetailId ASC
                      ROWS UNBOUNDED PRECEDING
                      ) as AccrualId
          FROM #TempTable
          WHERE #TempTable.PTORequestId = #TempParent.PTORequestId
          ) as AccrualDetails
  </code></pre>
  
  <p>I'm basically Trying to get <code>139</code> once per row from my Parent, but the FIRST_VALUE seems to return multiple results so it creating the Cartesian Product.</p>
  
  <p>So instead of Getting a single row result set </p>
  
  <p>PTORequestId: 48
  AccrualId:  139      </p>
  
  <p>First Value should only return a single Value
  Yet its returning 4 values.
  Why is this occurring, How can I make FIRST_VALUE return a single value per row?</p>
  
  <p><strong>EDIT</strong></p>
  
  <p>I think this is occuring because I'm selecting the FIRST_VALUE from the TempTable, so It's selecting the First Value for Each Row in the Temp Table. But I actually only to do it once</p>
  
  <p>For anyone who needs clarification on what I was doing wrong.
  I was expecting my Partition By To Filter Rows in my Table.
  I assumed since SUM on a Table will Return the sum of the entire table instead of the SUM of the Entire Table Per Row.  That sum work work based off of the partitioned data which is not the case.</p>