ORacle outer apply combination with group by

144 Views Asked by At

why this two queries have two different result :

  1. First query:
        select c.customer_id, oo.order_id
          from co.CUSTOMERS c
         outer apply (select *
                        from co.orders o
                       where o.customer_id = c.CUSTOMER_ID
                       order by o.order_id desc fetch first 1 rows only) oo
         where c.CUSTOMER_ID = 22
           and oo.order_id = 10000
  1. Second query:
        select c.customer_id, oo.order_id
          from co.CUSTOMERS c
         outer apply (select *
                        from co.orders o
                       where o.customer_id = c.CUSTOMER_ID
                       order by o.order_id desc fetch first 1 rows only) oo
         where c.CUSTOMER_ID = 22
           and oo.order_id = 10000
         group by c.customer_id, oo.order_id

the data of co.CUSTOMERS c

select * from co.CUSTOMERS c   where c.customer_id=22   
CUSTOMER_ID EMAIL_ADDRESS FULL_NAME
22 raymond.bailey@internalmail Raymond Bailey

the data of co.orders

select * from co.orders o where o.customer_id=22
order by o.order_id  desc 
ORDER_ID ORDER_DATETIME CUSTOMER_ID ORDER_STATUS STORE_ID
1819 11-MAR-19 07.20.17.942291 AM 22 COMPLETE 22
1435 07-JAN-19 03.57.14.316173 PM 22 COMPLETE 1
511 19-JUL-18 06.24.36.024192 AM 22 COMPLETE 1
184 26-APR-18 05.13.15.426435 AM 22 COMPLETE 1

First query return nothing
but Second query return below :

CUSTOMER_ID ORDER_ID
22 10000
1

There are 1 best solutions below

2
On

This is a bug. I've raised this for you.