CODEWITHSUNDEEP
sqloracle-databaseoracle12c
lokesh kumar
lokesh kumar

Reputation: 981

How to implement conditional select in sql query?

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

Upvotes: 0

Views: 93

Answers (1)

MT0
MT0

Reputation: 167932

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 );

Upvotes: 2

Related Questions