Select the first row in the last group of consecutive rows

user_7492289 0
Viewed 405

How would I select the row that is the first occurrence in the last 'grouping' of consecutive rows, where a grouping is defined by the consecutive appearance of a particular column value (in the example below state).

For example, given the following table:

id datetime state value_needed
1 2021-04-01 09:42:41.319000 incomplete A
2 2021-04-04 09:42:41.319000 done B
3 2021-04-05 09:42:41.319000 incomplete C
4 2021-04-05 10:42:41.319000 incomplete C
5 2021-04-07 09:42:41.319000 done D
6 2021-04-012 09:42:41.319000 done E

I would want the row with id=5 as it it is the first occurrence of state=done in the last (i.e. most recent) grouping of state=done.

greatest-n-per-group postgresql sql
2 Answers

Assuming all columns NOT NULL.

SELECT *
FROM   tbl t1
WHERE  NOT EXISTS (
   SELECT FROM tbl t2
   WHERE  t2.state <> t1.state
   AND    t2.datetime > t1.datetime
   )
ORDER  BY datetime
LIMIT  1;

db<>fiddle here

NOT EXISTS is only true for the last group of peers. (There is no later row with a different state.) ORDER BY datetime and take the first. Voilá.

user_9398600

Here's a window function solution that accesses your table only once (which may or may not perform better for large data sets):

SELECT *
FROM (
  SELECT *, 
    LEAD (state) OVER (ORDER BY datetime DESC) 
      IS DISTINCT FROM state AS first_in_group
  FROM tbl
) t
WHERE first_in_group
ORDER BY datetime DESC
LIMIT 1

A dbfiddle based on Erwin Brandstetter's. To illustrate, here's the value of first_in_group for each row:

id  datetime                 state       value_needed  first_in_group
---------------------------------------------------------------------
6   2021-04-12 09:42:41.319  done        E             f
5   2021-04-07 09:42:41.319  done        D             t
4   2021-04-05 10:42:41.319  incomplete  C             f
3   2021-04-05 09:42:41.319  incomplete  C             t
2   2021-04-04 09:42:41.319  done        B             t
1   2021-04-01 09:42:41.319  incomplete  A             t
user_5217990
Related