CODEWITHSUNDEEP
sqlgoogle-bigquery
user3447653
user3447653

Reputation: 4158

Query to get the first and last row in a table

I ran the following query to get the first and last row from a table.

SELECT Start, End, Start1, End1
FROM
(SELECT Start AS Start, End AS End 
  FROM [TB1]  WHERE Id = 251 ORDER BY DateTime DESC LIMIT 1),
(SELECT Start1 AS Start1, End1 AS End1 
  FROM [TB2]  WHERE Id = 251 ORDER BY DateTime LIMIT 1);

I get the output as follows:

 Start      End      Start1      End1
 25.32      -98.55    null       null
 null       null      29.81      -98.32

I am trying to get the output in the below format:

 Start      End      Start1      End1
 25.32      -98.55   29.81       -98.32

Upvotes: 1

Views: 135

Answers (3)

Elliott Brossard
Elliott Brossard

Reputation: 33745

If you want the first and last rows over a single table (rather than two), you can compute it using ARRAY_AGG with ORDER BY and LIMIT in standard SQL (uncheck "Use Legacy SQL" under "Show Options" in the UI).

WITH T AS (
  SELECT 251 AS Id, 10.0 AS Start, -15 AS `End`, DATE('2016-11-09') AS DateTime UNION ALL
  SELECT 251, 25.32 AS Start, -98.55, DATE('2016-11-10') UNION ALL
  SELECT 251, 29.81, -98.32, DATE('2016-01-01')
)
SELECT
  ARRAY_AGG(STRUCT(Start, `End`)
            ORDER BY DateTime DESC LIMIT 1)[OFFSET(0)] AS first_row,
  ARRAY_AGG(STRUCT(Start AS Start1, `End` AS End1)
            ORDER BY DateTime ASC LIMIT 1)[OFFSET(0)] AS last_row
FROM T
WHERE Id = 251;

In general, this will probably be a bit faster than using an analytic function, too, but I haven't done any specific comparisons.

Upvotes: 1

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172993

I feel like your question is "partial" and next will be how to do the same for all Id! See below 

SELECT Id, Start, End, Start1, End1
FROM
(SELECT Id, Start, End 
 FROM (
   SELECT
     Id, Start AS Start, End AS END, 
     ROW_NUMBER() OVER(PARTITION BY Id ORDER BY DateTime DESC) AS win
   FROM [TB1])
 WHERE win = 1
) AS t1
JOIN
(SELECT Id, Start1, End1 
 FROM (
   SELECT
     Id, Start1 AS Start1, End1 AS End1,
     ROW_NUMBER() OVER(PARTITION BY Id ORDER BY DateTime) AS win
   FROM [TB2])
 WHERE win = 1
) AS t2
ON t1.Id = t2.Id

Upvotes: 1

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

Use max so the result will be on one row.

SELECT max(Start) start, max(End) end, max(Start1) start1, max(End1) end1
FROM
(SELECT Start AS Start, End AS End 
  FROM [TB1]  WHERE Id = 251 ORDER BY DateTime DESC LIMIT 1),
(SELECT Start1 AS Start1, End1 AS End1 
  FROM [TB2]  WHERE Id = 251 ORDER BY DateTime LIMIT 1);

Upvotes: 1

Related Questions