I have the following table
x
-| id | name | index | number| answered| time |
-|-------|-------------|--------|-------|---------|-------------------|
-| 043b | callline2 | 1 | | TRUE |2020-05-26 11:07:25|
-| 043b | Holdline1 | 2 | | TRUE |2020-05-26 11:07:25|
-| 043b | Benny Russ | 3 | 505 | TRUE |2020-05-26 12:17:25|
-| 041b | callline2 | 1 | | FALSE |2020-05-26 10:17:25|
-| 041b | callline2 | 2 | | FALSE |2020-05-26 10:17:25|
-| 033b | callline2 | 1 | | FALSE |2020-05-26 10:17:25|
-| 033b | callline2 | 2 | | FALSE |2020-05-26 10:17:25|
I want to return
-| id | name | index | number| answered| End Time | Start Time |
-|-------|-------------|--------|-------|---------|-------------------|-------------------|
-| 043b | Benny Russ | 3 | 505 | TRUE |2020-05-26 12:17:25|2020-05-26 11:07:25|
-| 033b | callline2 | 2 | | FALSE |2020-05-26 10:17:25|2020-05-26 10:17:25|
-| 041b | callline2 | 2 | | FALSE |2020-05-26 10:17:25|2020-05-26 10:17:25|
what I have come up with so far is, the following. but I can’t figure out how to pull the name from the table when at the max index and where the number is not null?
SELECT distinct a.id , a.name, max.index_max, a.number, a.answered
, max.end_time, min.start_time
FROM example_table a
inner join (select Max(time) as end_time, max(index) as index_max, uuid
from example_table group by id) max on a.id = max.id
inner join (select min(time) as start_time, min(index) as index_min, uuid
from example_table group by id) min on a.id = min.id
Advertisement
Answer
Below is for BigQuery Standard SQL
#standardSQL
SELECT max_record.* EXCEPT(time),
max_record.time AS end_time,
start_time
FROM (
SELECT ARRAY_AGG(t ORDER BY index DESC LIMIT 1)[OFFSET(0)] AS max_record, MIN(time) AS start_time
FROM `project.dataset.table` t
GROUP BY id
)
if to apply to sample data from your question – output is