query to return single row for each ID

[Floyd Arguello]

Hi folks,

I'm trying to write a query that returns the latest row per ID. This works to an extent:

SELECT

  MAX(__time),

  ID,

  Status,

FROM table

It returns something similar to this:

__time,  ID,  Status
08:31,   001, SUCCESS  
08:30,   001, RUNNING  
08:31,   002, RUNNING
08:31,   003, RUNNING
08:29,   004, FAILED
08:28,   004, RUNNING

It returns the latest row per ID & Status.

How do I write the query so that it returns the latest row per ID, regardless of the status (or any other column for that matter)?

__time,  ID,  Status
08:31,   001, SUCCESS 
08:31,   002, RUNNING
08:31,   003, RUNNING
08:29,   004, FAILED

Thanks,

Floyd

[John Kowtko]

Hi Floyd,

Since Druid doesn't support SQL window functions (yet), I think you'll have to do a subquery with a group by to find the max time, and then join it back in.  Here I have written it as a CTE:

with max_time as (

 select ID, 

        max(__time) max_time

   from table

  group by ID

)

 select m.max_time,

        t.ID,

        t.Status

   from table t 

   join max_time m on t.ID = m.ID

If that doesn't cut it let me know.

Thanks.  John

[Floyd Arguello]

Thanks, John - that was really helpful.

[John Kowtko]

Oops I think I forgot to add the _time to the join condition,  so ...

with max_time as (

 select ID, 

        max(__time) max_time

   from table

  group by ID

)

 select m.max_time,

        t.ID,

        t.Status

   from table t 

   join max_time m on t.ID = m.ID and t.__time = m.max_time