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

Postgresql – SQL query to find items which are recently updated and not deleted

I have a table with the structure :

Code Time Delete indicator
A1 2023-07-26 17:00:00 1
A1 2023-07-25 15:00:00 0
A2 2023-07-26 11:00:00 0
A2 2023-07-25 18:00:00 1
A3 2023-07-22 10:00:00 1
A3 2023-07-26 9:00:00 0

I want a query which gives me all codes which have the most recent updated record with delete indicator as 1. The time can between a provided specific range like 25 july to 30 july.

Expected output :

Code Time Delete indicator
A1 2023-07-26 17:00:00 1

Output has A1 with time 2023-07-26 17:00:00 because it has delete indicator as 1. Older record should not be taken into consideration
A2 is not there because its most recent record of 2023-07-26 11:00:00 has delete indicator as 0
A3 is not there because its most recent record of 2023-07-26 9:00:00 has delete indicator as 0 and older record of 22 july is anyway out of range

Tried Group by Code but it didnt work because I want all columns in output

Tags:

2

Answers


  1. 0

    This should work

    select t1.*
from my_table t1
    left join my_table t2 on t1.Time > t2.Time
where t2.Code is null and t1.deleted = 1
    Login or Signup to reply.
  2. 0

    Products Table

    Try this (for two entry comparison):

    SELECT p1.* FROM products p1
left join products p2 on p2.code = p1.code
where p1.time > p2.time
and p1.is_deleted = 1;

    Try this (for multiple entry comparison):

    select * from products where (code, time) in 
(select code, max(time) from products group by code)
and is_deleted = 1;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search