title: Get first and last records in a table
body: <p>I have Id1 and Id2 with different STime and ETime. For each group of Id1 and Id2, I tend to get the first and last record (as shown in the output).
  I tried partitioning the table with Id1 and Id2 and order it by asc and desc order using:</p>
  
  <pre><code>ROW_NUMBER() OVER(PARTITION BY B.HardwareId, A.TripId ORDER BY StartTime) AS first_record,
  ROW_NUMBER() OVER(PARTITION BY B.HardwareId, A.TripId ORDER BY StopTime DESC) AS last_record
  </code></pre>
  
  <p>I did not get the results as expected. </p>
  
  <pre><code>SELECT
      A.Id1 AS Id1,
      A.Id2 AS TriId2pId,
      STime,
      ETime,
      Latitude,
      Longitude
  
  FROM
      Tr.T1 AS A
  JOIN
      Tp.G2 AS B
  ON
      A.STime &lt; B.DateTime
      AND A.ETime &gt;= B.DateTime
      AND A.Id1 = B.Id1
  WHERE
    (A._PARTITIONTIME BETWEEN TIMESTAMP('2016-11-23')
      AND TIMESTAMP('2016-11-23')
      AND A.Id1 IN (976))
  ORDER BY
     B.Id1,
     A.Id2,
     B.DateTime 
  
  
  
      Id1 Id2 STime               ETime               Latitude    Longitude
      976 715 2016-11-23 13:06:26 2016-11-23 13:23:30 26.2230015  -80.12314    
      976 715 2016-11-23 13:06:26 2016-11-23 13:23:30 26.2229767  -80.12326    
      976 715 2016-11-23 13:06:26 2016-11-23 13:23:30 26.2226944  -80.12344
      976 800 2016-11-23 13:06:26 2016-11-23 13:24:59 26.2251511  -80.11865    
      976 800 2016-11-23 13:06:26 2016-11-23 13:24:59 26.2251511  -80.11912    
      976 800 2016-11-23 13:06:26 2016-11-23 13:24:59 26.2250233  -80.11929    
      976 800 2016-11-23 13:06:26 2016-11-23 13:24:59 26.2245369  -80.11929        
  
  
      Id1 Id2 STime               ETime               Latitude    Longitude
      976 715 2016-11-23 13:06:26 2016-11-23 13:23:30 26.2230015  -80.12314
      976 715 2016-11-23 13:06:26 2016-11-23 13:23:30 26.2226944  -80.12344
      976 800 2016-11-23 13:06:26 2016-11-23 13:24:59 26.2251511  -80.11865
      976 800 2016-11-23 13:06:26 2016-11-23 13:24:59 26.2245369  -80.11929   
  </code></pre>