[firebird-support] Re: Firebird 2.5: Nested querys help
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 009 08/30/2017 09:20 0017 whatever 010 08/30/2017 09:30 0025 whatever 011 08/30/2017 09:40 0026 whatever 012 08/30/2017 09:50 0026 whatever 013 08/30/2017 10:00 0018 whatever 014 08/30/2017 10:10 0015 whatever 015 08/30/2017 10:20 0013 whatever 016 08/30/2017 10:30 0025 whatever 017 08/30/2017 10:40 0011 whatever 018 08/30/2017 10:50 0011 whatever 019 08/30/2017 11:00 0032 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 008 08/30/2017 09:10 0024 whatever 009 08/30/2017 09:20 0017 whatever 011 08/30/2017 09:40 0026 whatever 013 08/30/2017 10:00 0018 whatever 015 08/30/2017 10:20 0013 whatever 019 08/30/2017 11:00 0032 whatever Regards, Hernando.
[firebird-support] Re: Firebird 2.5: Nested querys help
Hi Set. Your query is working just as I needed. Good querys are the best querys as they are simpler. Thank you very much. Best regards. Hernando.
Re: [firebird-support] Re: Firebird 2.5: Nested querys help
Den 31.08.2017 21:01, skrev duque.herna...@yahoo.com [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