I have a report looks something like this :

This is done with SQL :

SELECT numberOrder, timestamp, id, status 
from order 
where status <> 'cancel'
order by id 
fetch first row only

The result return this : | numberOrder | timestamp | id | status | |:---- |:------:| -----:|-----:| | 12 | 2021-06-23-14.00.00.232425 | 11 |done |

How can I result the cancel order only in the same query if I don't have 2 or more orders. If I have only one order and the status is cancel (no order done). The result with only show the order with cancel. But if I have more than 2 orders (orders1 : done, orders2 : done and orders3 = cancel) The result should be only orders1 : done.

The report :

SELECT numberOrder, timestamp, id, status 
from order 
where (status <> 'cancel' or status = 'cancel') -- always true the condition
order by id 
fetch first row only

the result :

CodePudding user response：

If you are only ever selecting one row, ordering by status works,

SELECT numberOrder, timestamp, id, status 
from order
order by id, status DESC
fetch first row only

If you want to select an arbitrary number of rows, with cancel only if there are no "done",

SELECT numberOrder, timestamp, id, status 
from order
WHERE (status = 'done'
       or (SELECT status FROM order ORDER BY status DESC LIMIT 1) != 'done')
order by id

Not sure if the last solution is optimal, but it works.

CodePudding user response：

Try this:

WITH T (numberOrder, id, status) AS
(
VALUES
  (12, 11, 'done')
, (12, 22, 'done')
, (12, 23, 'done')
, (12, 25, 'cancel')

, (13, 10, 'cancel')
, (13, 11, 'cancel')
)
SELECT numberOrder, id, status
FROM
(
SELECT T.*, ROW_NUMBER () OVER (PARTITION BY numberOrder ORDER BY DECODE (status, 'cancel', 1, 0), id) AS RN_ 
FROM T
)
WHERE RN_ = 1

The result is:

If the result is unexpected, provide the correct one on the sample data above.