why this two queries have two different result :
- 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
- 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 |
This is a bug. I've raised this for you.