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

Postgresql – How can I remove duplicate id's based on the earliest datetime and then find the intersection of this list and another based on the same datetime?

So I have the following query

select * from sval where fid = 4044 and val = 'True';

The output of the above is:

sval table

id      fid               timestamp              val 
 1      4044        2019-01-22 00:00:00.000     True
 2      4044        2020-02-22 00:00:00.000     True 
 3      4044        2023-02-02 00:00:00.000     True 
 1      4044        2020-05-28 00:00:00.000     True
 4      4044        2023-05-03 00:00:00.000     True
 ....

I have another query:

select * from ival where fid = 3994 and val=0

The result of the query is:

ival table

id      fid                timestamp            val 
 1      3994        2019-01-22 00:00:00.000      0
 2      3994        2020-02-22 00:00:00.000      0 
 3      3994        2023-02-02 00:00:00.000      0 
 1      3994        2020-05-28 00:00:00.000      0
 4      3994        2023-05-03 00:00:00.000      0

I am trying to achieve 2 things:

  1. I need to filter the first table to remove duplicates id‘s and only keep the ‘earliest’ id, i.e. the one with the earliest timestamp.
  2. From this filtered list, I need to return a list of all the id‘s in the ival table that have the SAME timestamp as the result of 1)
Tags:

2

Answers


  1. 0

    If all you want to do is get the row with the least value in a group, you’ll want to use column = MIN(column) OVER(PARTITION BY groupingexpression) somewhere in the process.

    with 
sval (
    id
  , fid
  , timestamp
  , val
) as (
        select  1, 4044, cast('2019-01-22 00:00:00.000' as timestamp), 'True'
  union select  2, 4044, cast('2020-02-22 00:00:00.000' as timestamp), 'True'
  union select  3, 4044, cast('2023-02-02 00:00:00.000' as timestamp), 'True'
  union select  1, 4044, cast('2020-05-28 00:00:00.000' as timestamp), 'True'
  union select  4, 4044, cast('2023-05-03 00:00:00.000' as timestamp), 'True'
),
ival (
    id
  , fid
  , timestamp
  , val
) as (
        select 1, 3994, cast('2019-01-22 00:00:00.000' as timestamp), 0
  union select 2, 3994, cast('2020-02-22 00:00:00.000' as timestamp), 0
  union select 3, 3994, cast('2023-02-02 00:00:00.000' as timestamp), 0
  union select 1, 3994, cast('2020-05-28 00:00:00.000' as timestamp), 0
  union select 4, 3994, cast('2023-05-03 00:00:00.000' as timestamp), 0
),
svalFiltered as (
  select id
  , fid
  , timestamp
  , val
  , min(timestamp) over (partition by id) as timestampMin
  from sval
)

select 
  s.id
, s.fid
, s.timestamp
, s.val
, i.id as iid
, i.fid as ifid
, i.val as ival
from svalFiltered s
  inner join ival i on i.timestamp = s.timestamp

where s.timestamp = s.timestampMin
    Login or Signup to reply.
  2. 0

    Here is one way to do this:

    1. Filter the first table to keep only the earliest id for each fid.

    Use a common table expression (CTE) along with the ROW_NUMBER() function to assign a row number based on the timestamp for each fid. From ths, we can select the rows with row numbrs equal to 1 to get the earliest id for each fid.

    WITH RankedSval AS (
    SELECT 
        id, fid, timestamp, val,
        ROW_NUMBER() OVER (PARTITION BY fid ORDER BY timestamp) AS rn
    FROM sval
)
SELECT id, fid, timestamp, val
FROM RankedSval
WHERE rn = 1;
    1. Return a list of all the id’s in the ival table with the same timestamp as the result of Task 1.

    We use the same approach as before, but we also join it with the ival table to get the ids with the same timestamps.

    WITH RankedSval AS (
    SELECT 
        id, fid, timestamp, val,
        ROW_NUMBER() OVER (PARTITION BY fid ORDER BY timestamp) AS rn
    FROM sval
)
SELECT DISTINCT RankedSval.id as sval_id, ival.id as ival_id, ival.timestamp
FROM ival
JOIN RankedSval ON ival.timestamp = RankedSval.timestamp
WHERE RankedSval.rn = 1;

    Try it here

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

Back To Top
Search