SQL select rows-CodePudding

I have Oracle SQL Database with values like:

Date       Number    
20.04.22   1         
20.04.22   2 
20.04.22   3 
20.04.22   4 
20.04.22   5 
20.04.22   6 
21.04.22   1 
21.04.22   2 
21.04.22   3 
21.04.22   4
21.04.22   5 
21.04.22   6

Now, I want to select 3 rows, starting on number==value. The value is coming from somewhere else. For example, that value is 2, then I want to have 20.04.22 number 2-5.

How do I write a query if I want to have the numbers between two days? Like when the value is 6, then I need to have 20.04.22 number 6 and 21.04.22 number 1-2...

If its just one day, I can use where number >= [number i choose]

Thanks!

CodePudding user response：

I'm going to assume I understood your criteria correctly. We create a CTE to number the rows, determine the minimum row number for the selected value, and then pull the 3 rows starting from that row number.

with numbered as
(
    select t.*
        , row_number() over (partition by null order by "Date", "Number") rn
    from tbl t
)
select n."Date"
    , n."Number"
from numbered n
where n.rn >= (select min(rn) from numbered where "Number" = 6) --change value here (example using 6) to whatever value you need
    and rownum <= 3
order by n."Date", n."Number"

Example results:

CodePudding user response：

From Oracle 12, you can use:

SELECT "DATE", "NUMBER"
FROM   ( 
  SELECT t.*,
         COUNT(CASE "NUMBER" WHEN 6 THEN 1 END) OVER (ORDER BY "DATE", "NUMBER")
           AS cnt
  FROM   table_name t
  ORDER BY "DATE", "NUMBER"
)
WHERE cnt > 0
FETCH FIRST 3 ROWS ONLY;

Before Oracle 12, you can use:

SELECT "DATE", "NUMBER"
FROM   ( 
  SELECT t.*,
         COUNT(CASE "NUMBER" WHEN 6 THEN 1 END) OVER (ORDER BY "DATE", "NUMBER")
           AS cnt
  FROM   table_name t
  ORDER BY "DATE", "NUMBER"
)
WHERE cnt > 0
AND   ROWNUM <= 3;

(Note: DATE and NUMBER are reserved words and it is considered bad practice to use them as identifiers and, if you do, then must be quoted identifiers.)

Which, for your sample data:

CREATE TABLE table_name ("DATE", "NUMBER") AS
SELECT DATE '2022-04-20', 1 FROM DUAL UNION ALL
SELECT DATE '2022-04-20', 2 FROM DUAL UNION ALL
SELECT DATE '2022-04-20', 3 FROM DUAL UNION ALL
SELECT DATE '2022-04-20', 4 FROM DUAL UNION ALL
SELECT DATE '2022-04-20', 5 FROM DUAL UNION ALL
SELECT DATE '2022-04-20', 6 FROM DUAL UNION ALL
SELECT DATE '2022-04-21', 1 FROM DUAL UNION ALL
SELECT DATE '2022-04-21', 2 FROM DUAL UNION ALL
SELECT DATE '2022-04-21', 3 FROM DUAL UNION ALL
SELECT DATE '2022-04-21', 4 FROM DUAL UNION ALL
SELECT DATE '2022-04-21', 5 FROM DUAL UNION ALL
SELECT DATE '2022-04-21', 6 FROM DUAL;

Outputs:

DATE NUMBER

20-APR-22 6

21-APR-22 1

21-APR-22 2

DATE	NUMBER
20-APR-22	6
21-APR-22	1
21-APR-22	2

db<>fiddle here