0

How to fetch every 10th row from a table i.e. 10th,20th etc. But if there are less than 10 rows in that table, It should fetch the last row.

This can be an advance form of this query present in this question - How can I select 10th, 20th, 30th ... row of the result of another select query

I have done something like this for selecting every 10th row -

select case_id  
from 
  (select case_id,rownum as seq 
   from table 
   where from = 'A' and to ='B' 
   order by ID
   )
where mod(seq,10) = 0

But if there are less than 10 rows in the result of inner query, It should fetch the last row.

OBS: database - Oracle 12c

Community
  • 1
  • 1
lokesh kumar
  • 961
  • 1
  • 10
  • 18
  • 2
    I'm voting to close this question as off-topic because StackOverflow is not a code writing service. If you have code that doesn't work, post a question on SO. But "write this code for me" is off-topic here. – Luaan Feb 01 '16 at 11:00
  • I would create a temp table with rownum in, then write your script based on that temp table. – PKirby Feb 01 '16 at 11:01
  • The question has been edited, looks ok now. – Florin Ghita Feb 01 '16 at 13:08

1 Answers1

2
SELECT ColumnA,
       ColumnB,
       ColumnC
FROM   ( 
  SELECT ROWNUM rn,
         COUNT(*) OVER ( ORDER BY NULL ) mx,
         q.ColumnA,
         q.ColumnB,
         q.ColumnC
  FROM   (
    SELECT ColumnA,
           ColumnB,
           ColumnC
    FROM   your_table
    ORDER BY ColumnA
  ) q
)
WHERE  MOD( rn, 10 ) = 0
OR     ( mx = rn AND mx < 10 );
MT0
  • 143,790
  • 11
  • 59
  • 117