SQL select rows

Question

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!

The _first_ thing you should do is stop using 2-digit years. Is '21.04.22' supposed to be April 21, 2022, or April 22, 2021, or April 22, 1921, or . . . or . . . or . . . ? I spent a big chunk of my life in 1998-1999 remediating for Y2k. Please don't re-create it. — EdStevens, Apr 20 '22 at 16:45

MT0 · Answer 1 · 2022-04-20T14:25:30.443

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

db<>fiddle here

SQL select rows

1 Answers1