Get first and last records in a table
<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>