7 Replies Latest reply: Jul 11, 2013 5:35 AM by NickRobheart RSS

    How to select rows with min value on a specific column

    7c0ef6fb-c2ff-442e-b714-117beca05096

      I have a query:

      select

        m.x1,

        round (to_date (l.y1, 'mm/dd/yyyy HH:MI:SS AM') - m.x2, 0) as numofdays

      from

        table1 m,

        table2 l

      where

        l.x3 = m.x3 and

        to_date (l.y1, 'mm/dd/yyyy HH:MI:SS AM') >= TO_DATE('01012013','MMDDYYYY') and

        ...;

       

      and I got this result table:

      x1 (ID)numofdays
      0015

      001

      10
      0022
      0033
      0031
      0040
      00566
      ......

      several ID's have multiple values on the second column, I want to have only distinct IDs with smallest "numofdays" like this:

      x1 (ID)numofdays
      0015
      0022
      0031
      0040
      00566
      ......

       

      Any ideas?

        • 2. Re: How to select rows with min value on a specific column
          kordirko

          Use min and group by:

          select 
            m.x1,
            min( round (to_date (l.y1, 'mm/dd/yyyy HH:MI:SS AM') - m.x2, 0) ) as numofdays
          from 
            table1 m,
            table2 l
          where 
            l.x3 = m.x3 and
            to_date (l.y1, 'mm/dd/yyyy HH:MI:SS AM') >= TO_DATE('01012013','MMDDYYYY') and
            ...
          group by m.x1;
          
          • 3. Re: How to select rows with min value on a specific column
            Frank Kulash

            Hi,

             

            The most general and versatile way is a Top-N Query:

             

            WITH   got_r_num AS

            (

                select

                        m.x1,

                        round (to_date (l.y1, 'mm/dd/yyyy HH:MI:SS AM') - m.x2, 0) as numofdays

                ,       ROW_NUMBER () OVER ( PARTITION BY  m.x1

                                             ORDER BY      to_date (l.y1, 'mm/dd/yyyy HH:MI:SS AM') - m.x2

                                           )  AS r_num

                from 

                      table1 m,

                      table2 l

                where

                      l.x3 = m.x3 and

                      to_date (l.y1, 'mm/dd/yyyy HH:MI:SS AM') >= TO_DATE('01012013','MMDDYYYY') and

                      ...

            )

            SELECT  x1, numofdays

            FROM    got_r_num

            WHERE   r_num   = 1

            ;

             

            If you'd care to post CREATE TABLE and INSERT statements for your sample data, then I could test it.

            Notice that the sub-query (got_r_num) is exactly what you posted, only with a new column (r_num) added to the SELECT clause.

            • 5. Re: How to select rows with min value on a specific column
              NickRobheart

              I didn't mean to start or hijack any new thread but I have one specific question (not related to the original post though )

              Lets say, I have a table with distinct and common values both, and we would like to only get a table with all distinct values as an answer . ie

               

              ID:          1        2        3        4         5         6          7         8

              Name:   Nick  Nick1 Nick1  Nick2  Nick1  Nick3   Nick4  Nick1

               

              So now, I'd like to only get the table which only has distinct values (this should also include the value "Nick1" one time thus removing the rest repeated values )

              I tried with Distinct and Top but unable to think of query for the one mentioned. Is this retrieval possible ?

              • 6. Re: How to select rows with min value on a specific column
                Frank Kulash

                Hi,

                 

                NickRobheart wrote:

                 

                I didn't mean to start or hijack any new thread but I have one specific question (not related to the original post though )

                Lets say, I have a table with distinct and common values both, and we would like to only get a table with all distinct values as an answer . ie

                 

                ID:          1        2        3        4         5         6          7         8

                Name:   Nick  Nick1 Nick1  Nick2  Nick1  Nick3   Nick4  Nick1

                 

                So now, I'd like to only get the table which only has distinct values (this should also include the value "Nick1" one time thus removing the rest repeated values )

                I tried with Distinct and Top but unable to think of query for the one mentioned. Is this retrieval possible ?

                If you have a question that's not related to the original post, then the only ways to ask it are starting your own thread, or hijacking another thread.

                What's wrong with starting your own thread?  You'll get better replies faster.

                 

                Whenever you have a question, post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) and the results you want from that data.

                Explain, using specific examples, how you get those results from that data.

                Always say what version of Oracle you're using (e.g. 11.2.0.2.0).

                See the forum FAQ: https://forums.oracle.com/message/9362002#9362002

                • 7. Re: How to select rows with min value on a specific column
                  NickRobheart

                  I'm collating few set of question to post as a thread soon. So as of now I have this one question that I came across.

                  Is it possible to use SELECT DISTINCT and return all values from the table above( by removing the repeated values) ?

                  Or any other approach we have for it?