1

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

Andomar
  • 232,371
  • 49
  • 380
  • 404
Selçuklu Ebrar
  • 2,059
  • 3
  • 14
  • 11

1 Answers1

2
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.

Andomar
  • 232,371
  • 49
  • 380
  • 404
  • 2
    This will never return any rows, because you cannot use rownum = n with n > 1. See http://stackoverflow.com/questions/855422/oracle-sql-why-does-query-select-from-records-where-rownum-5-and-rownum – Frank Schmitt May 09 '13 at 11:05
  • 1
    @FrankSchmitt: Interesting, perhaps it can be done with `row_number()` – Andomar May 09 '13 at 11:07
  • Either that, or you return the rownum value from the inner query and use that value to filter in the outer one, sth like select * from (select rownum as rn, ... from ... ) where rn = (select round(cnt * 0.60) from yourtable. But I prefer the approach with windowing functions. – Frank Schmitt May 09 '13 at 11:13
  • My first answer didn't work in SQL Fiddle, would be surprised if it worked for you! – Andomar May 09 '13 at 11:21
  • You can adjust the `(order by ...)` after `row_number()` to adjust the ordering – Andomar May 09 '13 at 11:30
  • @Andamor excuse me, I tested your first answer is not working – Selçuklu Ebrar May 09 '13 at 11:36