Question 235604 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Retrieve entity_id based on specific conditions

I have a PostgreSQL table named entity_changes with the following structure:

CREATE TABLE entity_changes (
    entity_id INTEGER,
    raw_time BIGINT,
    resolved_datetime TIMESTAMP WITH TIME ZONE,
    old_phase VARCHAR(255),
    new_phase VARCHAR(255)
);

This table tracks changes in the phases of entities over time. Each row represents a change in the phase of an entity with the entity_id, along with timestamps and phase details.

I’m trying to formulate a query that retrieves entity_id values based on:

  1. The new_phase is ‘Accept’.
  2. There is no subsequent change from ‘Accept’ to ‘Review’ for the same entity_id.

For example, given the sample data below:

entity_id raw_time resolved_datetime old_phase new_phase
1 1683722752681 2023-05-10T14:45:52.681+02 NULL Log
1 1683722755440 2023-05-10T14:45:55.44+02 Log Approve
1 1683722758915 2023-05-10T14:45:58.915+02 Approve Fulfill
1 1687773706503 2023-06-26T12:01:46.503+02 Fulfill Accept
1 1687774413272 2023-06-26T12:13:33.272+02 Accept Review
2 1683722752681 2023-05-10T14:45:52.681+02 NULL Log
2 1683722755440 2023-05-10T14:45:55.44+02 Log Approve
2 1683722758915 2023-05-10T14:45:58.915+02 Approve Accept
2 1687773706503 2023-06-26T12:01:46.503+02 Accept Review
2 1687774413272 2023-06-26T12:13:33.272+02 Review Accept

I would like to retrieve the entity_id values 2, since for entity_id 1, there’s a subsequent change from ‘Accept’ to ‘Review’.

Could you please help me formulate a SQL query to achieve this?

db fiddle : https://www.db-fiddle.com/f/fNLaxsA1MFM5oSWztmqfad/0

Tags:

2

Answers


  1. 0

    Try the following

    SELECT * FROM entity_changes
WHERE new_phase='Accept' AND raw_time 
 IN(SELECT MAX(raw_time) FROM 
 entity_changes GROUP BY entity_id)
ORDER BY entity_id, raw_time;
    Login or Signup to reply.
  2. 0

    Not exist is probably the easiest way to go.

    select * 
  from entity_changes t1
 where t1.new_phase = 'accept'
   and not exists
         ( select 1
             from entity_changes t2
            where t2.entity_id = t1.entity_id
              and t2.resolved_datetime > t1.resolved_datetime
              and t2.new_phase = 'reject'
         )
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search