2 Replies Latest reply: Jul 12, 2013 2:16 PM by doctordba RSS

    select a first row which contains a MAX value

    1001135

      Hello all,

      create table toto ( name varchar2(1), b varchar2(4), c number);

      insert into toto values ( 'a','toto',1);

      insert into toto values ( 'a','fifi',10);

      insert into toto values ( 'a','gigi',999);

      insert into toto values ( 'a','lili',999);

      insert into toto values ( 'b','mimi',3);

      insert into toto values ( 'b','vivi',300);

       

      I'm searching for a query which returns a FIRST row which contains MAX(c) for each name :

      ie :

                 a  gigi 999              ( or "a lili 999"          I don't care !!)

                 b  vivi  300

       

      Thank you in advance.

        • 1. Re: select a first row which contains a MAX value
          Paul M.

          I'm searching for a query which returns a FIRST row which contains MAX(c) for each name :

          ie :

                     a  gigi 999              ( or "a lili 999"          I don't care !!)

                     b  vivi  300

           

          Maybe this one ?

          SQL> select name,min(b),c from toto t

            2  group by name,c

            3  having c=(select max(c) from toto where name=t.name);

          • 2. Re: select a first row which contains a MAX value
            doctordba

            Try this:

             

            SELECT DISTINCT

                          first_value(b) over (PARTITION BY name ORDER BY c DESC) b,

                          first_value(name) over (PARTITION BY name ORDER BY c DESC) name,

                          first_value(c) over (PARTITION BY name ORDER BY c DESC) c

            FROM toto;