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

Postgresql – How can I filter a list of id's by comparing values of the same id's in a different table?

I have the following SQL Postgres database table:

    id      fid               val
401411677   3765    2019-09-06 00:00:00.000
401411677   3782    2019-09-06 00:00:00.000
401413382   3765    2019-12-20 00:00:00.000
401413382   3782    2019-12-23 00:00:00.000
401412111   3765    2019-09-20 00:00:00.000
401412111   3782    2019-09-20 00:00:00.000

In the above, an fid = 3765 represents the FirstDate and and fid=3782 is the SecondDate.

I am trying to filter out all the id’s where the FirstDate and SecondDate are equal.

So in the above example, the output would be only 401413382, since it’s FirstDate and SecondDate values are not the same, but for the other id’s they are the same.

Tags:

2

Answers


  1. 0

    That could be achieved with windowing functions or even without:

    with start_event as
(
    select id, val
    from events
    where fid = 3765
),
end_event as 
(
    select id, val
    from events
    where fid = 3782
)
select e.id as id, s.val start_event, e.val end_event
from start_event s
inner join end_event e
on e.id = s.id
where
    s.val <> e.val;

    Link to Fiddle

    Login or Signup to reply.
  2. 0

    Using two subqueries:

    select distinct t.id from tbl t where 
 (select min(t1.val) from tbl t1 where t1.id = t.id and t1.fid = 3765) !=
 (select min(t1.val) from tbl t1 where t1.id = t.id and t1.fid = 3782)

    See fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search