Oracle sql to query last N rows as rest of dividing by fixed value-CodePudding

Suppose I have something like this:

CREATE TABLE "PIPPO" ("COLUMN1" number);
   
INSERT INTO PIPPO (COLUMN1) VALUES (1);
INSERT INTO PIPPO (COLUMN1) VALUES (2);
INSERT INTO PIPPO (COLUMN1) VALUES (3);
INSERT INTO PIPPO (COLUMN1) VALUES (4);
INSERT INTO PIPPO (COLUMN1) VALUES (5);
INSERT INTO PIPPO (COLUMN1) VALUES (6);
INSERT INTO PIPPO (COLUMN1) VALUES (7);
INSERT INTO PIPPO (COLUMN1) VALUES (8);

Is there an "easy" way to run a SELECT query on the table to get the last N rows, where N is the rest of divide by 3?

For example for this case I would like o retrieve:

7
8

Imagine to insert another record

   INSERT INTO PIPPO (COLUMN1) VALUES (9);

In this case, I would like to retrieve:

7
8
9

Imagine inserting yet another record:

   INSERT INTO PIPPO (COLUMN1) VALUES (10);

Now in this case I would like to retrieve just:

Any ideas?

CodePudding user response：

Let the innermost subquery return the number of rows to return. Use FETCH FIRST to get that number of rows:

select * from
(
    select * from PIPPO 
    order by COLUMN1 desc
    fetch first (select mod(count(*)-1, 3)   1 from PIPPO) rows only
) dt
order by COLUMN1

CodePudding user response：

You can use FETCH FIRST n ROWS ONLY and use a sub-query to get the n value:

SELECT *
FROM   pippo
ORDER BY column1 DESC
FETCH FIRST (SELECT DECODE(MOD(COUNT(*), 3), 0, 3, 1, 1, 2, 2) FROM pippo) ROWS ONLY

or you can use analytic functions:

SELECT column1
FROM   (
  SELECT column1,
         ROW_NUMBER() OVER (ORDER BY column1 DESC) AS rn,
         MOD(COUNT(*) OVER (), 3) AS rem
  FROM   pippo p
)
WHERE  rn <= DECODE(rem, 0, 3, rem);

fiddle