oracle can make consecutive identical items appear only once?

Question

show the consecutively item only once

like:

data:                     result
number   date                 number   date
-----------------        ==>  -----------------
1      2022/02/20               1    2022/02/20
2      2022/02/19               2    2022/02/19              
1      2022/02/18               1    2022/02/16
1      2022/02/17               2    2022/02/15
1      2022/02/16           
2      2022/02/15

Answer 1

This is a gaps and islands problem. Your logic seems to be to report a single number value for each group of records sharing the same number value along a range of date values, along with the minimum date value for that island of numbers. The following answer uses the different in row numbers method:

WITH cte AS (
    SELECT t.*, ROW_NUMBER() OVER (ORDER BY "date") rn1,
                ROW_NUMBER() OVER (PARTITION BY "number" ORDER BY "date") rn2
    FROM yourTable t
)

SELECT "number", MIN("date") AS "date"
FROM cte
GROUP BY "number", rn1 - rn2
ORDER BY 2 DESC;

Demo