Search code examples
sqloracle-databasetop-n

SQL - How to select a row having a column with max value without using sub query in Oracle

      date         value
    18/5/2010        40
    18/5/2010        20
    20/5/2010        60
    18/5/2010        30
    17/5/2010        10
    16/5/2010        40
    18/5/2010        60
    18/5/2010        25

Output

date           value
18/5/2010        60
20/5/2010        60

I need to query for the row having max(value)(i.e. 60). So, here we get two rows.
the date can be in any order

Plz do not use SUBQUERY

I need a dynamic query without using sub query

NESTED QUERY will be fine ...

I have tried that using rownum ... Where rownum< some_value ...but it is not dynamic

Solution

  • In 12c the FETCH clause allows you to do this

    select * from the_table
 order by value desc
 FETCH FIRST 1 ROWS WITH TIES;