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

Community
  • 1
  • 1
mayank
  • 25
  • 1
  • 5
  • 1
    have you tried using `MAX`? [Here](https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions085.htm) is a link that will help you get started if you're not familiar. – Aaron Aug 10 '17 at 16:56
  • Is that a joke? I already did that .. Using sub query now i want it to be solved through single or nested query read it carefully.... So max over partition by i hv done that logic... – mayank Aug 10 '17 at 17:00
  • 3
    Also, do you make a habit of insulting people who are trying to help you? How does that work out generally? – APC Aug 10 '17 at 17:06
  • First of all i got the solution and i'm not insulting him, just telling him to read my q once again ... – mayank Aug 10 '17 at 17:10
  • 1
    @apc I think the -4 score on his question answers yours... – SandPiper Aug 10 '17 at 20:19
  • 1
    Let's make that -5. – William Robertson Aug 10 '17 at 23:19

1 Answers1

0

In 12c the FETCH clause allows you to do this

select * from the_table
 order by value desc
 FETCH FIRST 1 ROWS WITH TIES;
APC
  • 144,005
  • 19
  • 170
  • 281
Oto Shavadze
  • 40,603
  • 55
  • 152
  • 236