SQL select unique name based on max index

Robin Fay Published at Java

Robin Fay

I have the following table

-| 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

Mikhail Berlyant

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