SQL: How to select the oldest date row-CodePudding

I have a report looks something like this :

numberOrder	timestamp	id	status
12	2021-06-23-14.00.00.232425	11	done
13	2021-06-30-18.00.00.224525	22	done
14	2021-07-01-01.00.00.224525	23	done

This is done with SQL :

SELECT numberOrder, timestamp, id, status 
from order 
where status = 'done'

I would like the report to show just the oldest row :

numberOrder	timestamp	id	status
12	2021-06-23-14.00.00.232425	11	done

SELECT numberOrder, timestamp, id, status 
from order 
WHERE timestamp = (select TOP 1 timestamp FROM order by timestamp)
  and status = 'done'`

Any ideas ? I try to use min(). Any help is appreciated And have any ideas if I dont find any status done then find the status cancel ?

I would like the report to show just the cancel row if we dont find any done status :

numberOrder	timestamp	id	status
12	2021-06-23-14.00.00.232425	11	cancel

CodePudding user response：

Admittedly unfamiliar with DB2 but I would suggest the following to order the rows by timestamp and fetch the first (oldest) row.

select numberOrder, timestamp, id, status 
from order 
where status = 'done'
order by timestamp
fetch first 1 rows only

CodePudding user response：

Try the following query.
You may run the statement as is if you remove group comments /* */.
The statement without this group comments work on rows with cancel statuses only in the base "table" MYTAB. You may comment out the row with 'cancel' and uncomment the row with 'done' inside VALUES to get all rows with statuses done only in the base "table". Or construct your base "table" with whatever contents of your original query.
You don't have to edit your query to get both results.
The idea is to enumerate rows row inside both status types. The first rows (ordered by timestamp) in all statues ordered to have the done row before the cancel one. Finally we get the first row only.

WITH 
/*
  MYTAB0 (numberOrder, timestamp, id) AS
(
  VALUES
    (12, '2021-06-23-14.00.00.232425', 11)
  , (13, '2021-06-30-18.00.00.224525', 22)
  , (14, '2021-07-01-01.00.00.224525', 23)
)
, MYTAB AS
(
SELECT *
FROM MYTAB0, 
(
VALUES
'cancel'
--'done'
) S (STATUS)
)
, 
*/
T AS
(
  SELECT T.*, ROW_NUMBER () OVER (PARTITION BY STATUS ORDER BY TIMESTAMP) AS RN_
  FROM MYTAB T
  WHERE STATUS IN ('done', 'cancel')
)
SELECT numberOrder, timestamp, id, status
FROM T
WHERE RN_ = 1
ORDER BY DECODE (status, 'done', 0, 1)
FETCH FIRST 1 ROW ONLY