CODEWITHSUNDEEP
sqlsqlite
Tafarel Chicotti
Tafarel Chicotti

Reputation: 163

Get only rows when column is duplicated and timestamps get a difference between them

I have to figured out how get only the rows within duplicated values but differents timestamps. Each timestamp should be between -1/+1 minutes of diff at maximum.

id client_id amount token sales_ref_id updated_at
1 29238 6.333 a$idjqb5AUdnh34 380 2020-11-17 18:45
2 2008 200 kla3@nans#9aisD 9 2020-11-17 18:45
3 2008 200 4843$a1s00c4544 9 2020-11-17 18:46
4 2008 -200 va$ie32u*Asc2001 9 2020-11-17 18:48
5 1800 6.333 a$id4843$0c45c44 19 2020-11-17 19:44

Like the data above, I should get only the rows #2 and #3, because they have the same client, amount, sales_ref_id and only have 1 minutes plus or less of difference.

But I dont have any clue how should I start to a close solution.

Upvotes: 0

Views: 71

Answers (1)

forpas
forpas

Reputation: 164099

Yo can use EXISTS:

SELECT t1.*
FROM tablename t1
WHERE EXISTS (
  SELECT 1
  FROM tablename t2
  WHERE t2.id <> t1.id
    AND (t2.client_id, t2.amount, t2.sales_ref_id) = (t1.client_id, t1.amount, t1.sales_ref_id)
    AND ABS(strftime('%s', t2.updated_at) - strftime('%s', t1.updated_at)) <= 60
);

See the demo.

Upvotes: 1

Related Questions