CODEWITHSUNDEEP
sqlamazon-timestream
ArielB
ArielB

Reputation: 1274

In SQL language (AWS timestream) - how to get the latest value of non-grouped by column? (latest - meaning the most recent row)

My goal - is to group my data by a specific field, and select also the latest value from another field on that group.

I have the following table:

| type | count | action | time                         |
   a       5      put     2021-04-21 15:13:02.104308224 
   b       7      put2    2021-04-20 15:13:02.104308224 
   b       1      get     2021-04-19 15:13:02.104308224 
   a       4      put6    2021-04-18 15:13:02.104308224 
   c       5      get     2021-04-17 15:13:02.104308224 
   a       6      put     2021-04-17 15:13:02.104308224

My statement is as follows:

 SELECT
    type,
    SUM(count) as total,
FROM mydb.mytable
WHERE time between ago(1h) and now()
GROUP type

I will eventually get the following rows:

| type | total
   a      15  
   b      8
   c      5

The thing is, I need to add to each row (that was grouped) also the action of the most recent row - so it should be

| type | total | action
   a      15      put
   b      8       put2
   c      5       get

how can it be achieved? Thanks!

Upvotes: 1

Views: 2491

Answers (1)

eshirvana
eshirvana

Reputation: 24603

if it supports window functions :

select 
   type,
   lastaction, 
   SUM(count) as total
  from (
    SELECT   *, 
    first_value(action) over (partition by type order by time desc) lastaction
    FROM mydb.mytable
    WHERE time between ago(1h) and now()
)
GROUP BY type, lastaction

Upvotes: 3

Related Questions