I have a sample table like this:

I want:

The time selected is not important, the GROUP BY or DISTINCT ON should be on a range of 1 second.

EDIT: All code within 1 second of each other are considered duplicate and only one row of them should be selected.

CodePudding user response：

Assuming you have a table called codes which has two columns (code and time), the following query will produce the exact result you're looking for (as per above tables):

WITH dup_codes AS (
  SELECT code, time FROM codes c1
   WHERE EXISTS (SELECT  1 from codes c2
              WHERE c2.code = c1.code
              -- this is where magic happens!
              AND c2.time   interval '1 second' = c1.time
             )
)

SELECT * FROM Codes
WHERE time NOT IN (SELECT time FROM dup_codes);

Db Fiddle: https://dbfiddle.uk/?rdbms=postgres_13&fiddle=c9c4def09b06f04f77d5fa71f29e6c57