Postgres Multiple select

Question

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'
         );

Your query looks fine, except that you run the same subselect twice (maybe try WHERE (SELECT ...) BETWEEN start_epoch AND end_epoch). Can you provide sample data to prove that the query does not do what you want? Can you verify that the subselect returns a single row and not NULL? — Laurenz Albe
– Laurenz Albe, Commented May 14, 2017 at 4:33
Thanks for your help. I am getting the following "ERROR: more than one row returned by a subquery used as an expression" — user3896519
– user3896519, Commented May 14, 2017 at 4:48

Gordon Linoff · Accepted Answer · 2017-05-14 04:43:09Z

1

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;

answered May 14, 2017 at 4:43

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user3896519 Over a year ago

Hi Gordon, Thank you so much. This solved my problem. Just 1 further question, is there any way to only list if count(uuid) is greater than 5?

Glenn Over a year ago

append to the query: having count(uuid) > 5

Gordon Linoff Over a year ago

@user3896519 . . . I'm not sure what that means. This returns only one column and one row. What should it return if the count is not greater than 5?

Collectives™ on Stack Overflow

Postgres Multiple select

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related