CODEWITHSUNDEEP
sqlpostgresql
user3896519
user3896519

Reputation: 515

Postgres Multiple select

I have a table called v_xml_cdr and have a the following columns: uuid, start_epoch and end_epoch. I want to be able to query the table for the start_epoch of of a uuid and then take that uuid and query the table again for all calls that have a start_epoch of less than or equal to the start_epoch from the previously found uuid and an end_epoch of greater than or equal to that same previously found start_epoch. Here is what I have so far, but it returns an empty result set. It should return 5 rows.

select count(uuid)
from v_xml_cdr
where start_epoch
      <= (select start_epoch as reject_start
          from v_xml_cdr
          where uuid = '5c076428-3790-11e7-868a-xxxxx'
         )
  and end_epoch
      >= (select start_epoch as reject_start
          from v_xml_cdr
          where uuid = '5c076428-3790-11e7-868a-xxxxx'
         );

Upvotes: 1

Views: 232

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269553

I think you want this:

select count(uuid)
from v_xml_cdr xc join
     (select min(start_epoch) as minse
      from v_xml_cdr xc1
      where uuid = '5c076428-3790-11e7-868a-xxxxx'
     ) xc1
     on xc.start_epoch <= xc1.minse and
        xc.end_epoch >= xc1.minse;

Upvotes: 1

Related Questions