[firebird-support] Re: Firebird 2.5: Nested querys help

2017-08-31 Thread 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
   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

2017-09-01 Thread duque.herna...@yahoo.com [firebird-support] 
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

2017-08-31 Thread setysvar setys...@gmail.com [firebird-support] 

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