As a beginner to sql, I have a table like below in PostgreSQL.
id product type occured_at
1 A 6 2017-03-17 10:21:22.935278
1 B 6 2017-04-17 10:21:22.941801
1 B 8 2017-04-17 10:21:22.935278
1 B 8 2017-05-17 10:21:22.935278
1 D 8 2017-06-17 10:21:22.935278
2 C 4 2017-04-24 10:21:22.938517
3 A 8 2017-04-27 10:21:22.941801
4 C 8 2017-09-17 10:21:22.941801
4 C 6 2017-09-17 10:21:22.941801
5 D 8 2017-09-17 10:21:22.941801
The question is how can I get the id and product which has different types that occurred on the same date. Just like
id product
1 B
4 C
If anyone can help me out, I really appreciate it :)
A self join is one approach:
SELECT DISTINCT A.ID, A.Product
FROM YourTable A
JOIN YourTable B ON A.ID = B.ID
AND A.Product = B.Product
AND DATE(A.Occured_at) = DATE(B.Occured_at)
AND A.Type <> B.Type
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments