CODEWITHSUNDEEP
sqloracle-database
Selçuklu Ebrar
Selçuklu Ebrar

Reputation: 2269

Get the row at 60% of the table

I'd like to select the row at 60% of a table with Oracle. I can find the number of the 60% row with:

select round(count(*)*0.60) as sira from 
    (select to_date(time) as tarih,lenght as hiz  from table order by length desc)

I'm looking for the name column data for the row at 60%. That is, 60% of all rows should have a higher length than the selected row.

For example, for this data:

name   time    length
r1     10:00   1
r2     10:02   2
r3     10:04   3
...
r10    10:20   10

I'm looking for a query that prints r4 (row at 60% ordered by decreasing length.)

Upvotes: 1

Views: 423

Answers (1)

Andomar
Andomar

Reputation: 238078

select  *
from    (
        select  row_number() over (order by yt.length desc) as rn
        ,       count(*) over () as cnt
        ,       yt.*
        from    YourTable yt
        ) SubQueryAlias
where   rn = round(cnt * 0.60)

Example at SQL Fiddle.

Upvotes: 2

Related Questions