CODEWITHSUNDEEP
google-bigquery
space_balls
space_balls

Reputation: 1423

BigQuery get latest record when grouping id, sorted by date

I've connected a ticketing system into BigQuery and I now want to output a report.

The issue I have is that every reply on the ticket is added into a new row. Something like this:

ticket_id | subject | status | date
1         | help    | open      | 2019-08-01
1         | help    | closed    | 2019-08-02
2         | problem | open      | 2019-08-01
2         | problem | open      | 2019-08-02
2         | problem | open      | 2019-08-03
2         | problem | open      | 2019-08-04

Desired output:

ticket_id | subject | status    | date
1         | help    | closed    | 2019-08-02
2         | problem | open      | 2019-08-04

What query do I use to get the desired output? I tried something like this

SELECT * FROM [table_name] 
ORDER BY date
GROUP BY ticket_id

but I get the error:

SELECT list expression references column status which is neither grouped nor aggregated at [1:12]

Upvotes: 2

Views: 4843

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172993

Below is for BigQuery Standard SQL 

#standardSQL
SELECT AS VALUE ARRAY_AGG(t ORDER BY `date` DESC LIMIT 1)[OFFSET(0)]
FROM `project.dataset.table` t
GROUP BY ticket_id

You can test, play with above using sample data from your question as in below example 

#standardSQL
WITH `project.dataset.table` AS (
  SELECT 1 ticket_id, 'help' subject, 'open' status, DATE '2019-08-01' `date` UNION ALL
  SELECT 1, 'help', 'closed', '2019-08-02' UNION ALL
  SELECT 2, 'problem', 'open', '2019-08-01' UNION ALL
  SELECT 2, 'problem', 'open', '2019-08-02' UNION ALL
  SELECT 2, 'problem', 'open', '2019-08-03' UNION ALL
  SELECT 2, 'problem', 'open', '2019-08-04' 
)
SELECT AS VALUE ARRAY_AGG(t ORDER BY `date` DESC LIMIT 1)[OFFSET(0)]
FROM `project.dataset.table` t
GROUP BY ticket_id

with result 

Row ticket_id   subject status  date     
1   1           help    closed  2019-08-02   
2   2           problem open    2019-08-04

Upvotes: 9

Related Questions