CODEWITHSUNDEEP
google-bigquery
user3447653
user3447653

Reputation: 4158

Get first and last records in a table

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:

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

I did not get the results as expected. 

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 < B.DateTime
    AND A.ETime >= 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

Upvotes: 0

Views: 2535

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173028

Query in your original question does not exactly match you later comments, but still below should help you to make your goal

Try 

SELECT
   A.Id AS Id,
   StartTime,
   StopTime,
   Latitude,
   Longitude
FROM (
  SELECT
   A.Id AS Id,
   StartTime,
   StopTime,
   Latitude,
   Longitude,
   ROW_NUMBER() OVER(PARTITION BY id ORDER BY StartTime) AS first_record,
   ROW_NUMBER() OVER(PARTITION BY id ORDER BY StartTime DESC) AS last_record
  FROM
   Tb1.Ids  AS A
  JOIN
   Tb2.Points AS B
  ON
   A.StartTime <= B.DateTime
   AND A.StopTime >= B.DateTime
   AND A.HardwareId = B.HardwareId
  WHERE
   (A._PARTITIONTIME BETWEEN TIMESTAMP('2016-11-23')
    AND TIMESTAMP('2016-11-23'))
   AND A.Id IN (334)
)
WHERE first_record = 1 OR last_record = 1

Idea here to add two fields in inner query that would numbering all rows from start and from end 

   ROW_NUMBER() OVER(PARTITION BY id ORDER BY StartTime ) AS first_record,
   ROW_NUMBER() OVER(PARTITION BY id ORDER BY StartTime DESC) AS last_record

and than to leave the first and respectively last rows 

WHERE first_record = 1 OR last_record = 1

Upvotes: 1

Related Questions