CODEWITHSUNDEEP
sqlgoogle-bigquery
Robin Fay
Robin Fay

Reputation: 33

SQL select unique name based on max index

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

Upvotes: 2

Views: 86

Answers (2)

marcothesane
marcothesane

Reputation: 6741

I'd do it using classic, ANSI SQL. With window functions, nesting a query:

WITH
-- your input, copy-pasted and reformatted ... Thanks for that ...
indata(id,name,index,number,answered,time) AS (
          SELECT '043b','callline2' ,1,NULL,TRUE ,TIMESTAMP '2020-05-26 11:07:25'
UNION ALL SELECT '043b','Holdline1' ,2,NULL,TRUE ,TIMESTAMP '2020-05-26 11:07:25'
UNION ALL SELECT '043b','Benny Russ',3,505 ,TRUE ,TIMESTAMP '2020-05-26 12:17:25'
UNION ALL SELECT '041b','callline2' ,1,NULL,FALSE,TIMESTAMP '2020-05-26 10:17:25'
UNION ALL SELECT '041b','callline2' ,2,NULL,FALSE,TIMESTAMP '2020-05-26 10:17:25'
UNION ALL SELECT '033b','callline2' ,1,NULL,FALSE,TIMESTAMP '2020-05-26 10:17:25'
UNION ALL SELECT '033b','callline2' ,2,NULL,FALSE,TIMESTAMP '2020-05-26 10:17:25'
)
,
w_rn AS (
  SELECT
    *
  , ROW_NUMBER() OVER(PARTITION BY id ORDER BY index DESC) AS rn
  FROM indata
)
SELECT
  id
, name
, index
, number
, answered
, time
FROM w_rn
WHERE rn = 1;
-- out   id  |    name    | index | number | answered |        time         
-- out ------+------------+-------+--------+----------+---------------------
-- out  033b | callline2  |     2 |        | f        | 2020-05-26 10:17:25
-- out  041b | callline2  |     2 |        | f        | 2020-05-26 10:17:25
-- out  043b | Benny Russ |     3 |    505 | t        | 2020-05-26 12:17:25

Upvotes: 2

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173028

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

enter image description here

Upvotes: 2

Related Questions