This discussion is archived
7 Replies Latest reply: Jul 11, 2013 3:35 AM by NickRobheart RSS

How to select rows with min value on a specific column

7c0ef6fb-c2ff-442e-b714-117beca05096 Newbie
Currently Being Moderated

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 Pro
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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?

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points