Den 31.08.2017 21:01, skrev [email protected] [firebird-support]:
Set, thank you for your help.
I'll like to explain my self better. If having these records:
table_id date_time customer_id other_fields
-------- ---------------- ----------- ------------
001 08/30/2017 08:00 0015 whatever
002 08/30/2017 08:10 0025 whatever
003 08/30/2017 08:20 0025 whatever
004 08/30/2017 08:30 0011 whatever
005 08/30/2017 08:40 0014 whatever
006 08/30/2017 08:50 0025 whatever
007 08/30/2017 09:00 0021 whatever
008 08/30/2017 09:10 0024 whatever
...
What I need is to select only the first record (based on date_time)
from each customer_id. Like this:
table_id date_time customer_id other_fields
-------- ---------------- ----------- ------------
001 08/30/2017 08:00 0015 whatever
002 08/30/2017 08:10 0025 whatever
004 08/30/2017 08:30 0011 whatever
005 08/30/2017 08:40 0014 whatever
007 08/30/2017 09:00 0021 whatever
...
OK Hernando, then it is simply
SELECT xx0.*
FROM my_table xx0
WHERE NOT EXISTS(SELECT *
FROM my_table xx1
WHERE XX0.customer_id = XX1.customer_id
AND (XX0.date_time > XX1.date_time
OR (XX0.date_time = XX1.date_time
AND XX0.table_id > XX1.table_id)))
What's after the OR is fairly unlikely to happen, it covers the case of
identical timestamps. It is up to you whether you want the last two
lines to cover this or only keep the first six lines.
HTH,
Set
