Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2002
    Posts
    227

    Unanswered: query with min value

    Hi,
    my table TAB_ID:

    id..........desc_id.......num_id......count_id
    1............AAAA.........1.............2
    3............bbb..........2.............3
    7............sss..........1.............5
    4............ttt..........6.............7
    9............mmm..........3.............1
    8............llll.........3.............2
    10............dddd........4.............6
    12............yyyyy.......4.............5
    15............rrrr........4.............7
    16............nnnnn.......4.............3

    I'd like an output with these options:
    if there are same num_id I must take min(count_id)

    For example: id=1 has count_id=2 and id=7 has count_id=5 with same num_id=1, in this case I must take count_id=2

    id=9 has count_id=1 and id=8 has count_id=2 with same num_id=3, in this case I must take count_id=1

    therefore, for my table TAB_ID, the output will:

    id..........desc_id.......num_id......count_id
    1............AAAA.........1.............2

    3............AAAA.........2.............3

    4............AAAA.........6.............7

    9............AAAA.........3.............1

    16............AAAA.........4.............3


    How can i write this query?

    Thanks in advance!

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    analytics. read up on it for some great ways to manipulate data:
    PHP Code:
    select iddesc_idnum_idcount_id
    (select 
       id
    desc_idnum_idcount_id,
       
    count(*) over (partition by num_id order by num_idcount_idrolling_total
     from tab_id
    )
    where rolling_total 1
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Dec 2003
    Posts
    1,074
    Code:
    select id, desc_id, num_id, count_id 
    from   tab_id t1
    where  count_id = (select min(t2.count_id) 
                       from   tab_id t2 
                       where  t1.num_id = t2.num_id)
    This has the potential to give duplicates if the count_id and num_id are the same across records.

    -cf

  4. #4
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Quote Originally Posted by chuck_forbes
    Code:
    select id, desc_id, num_id, count_id 
    from   tab_id t1
    where  count_id = (select min(t2.count_id) 
                       from   tab_id t2 
                       where  t1.num_id = t2.num_id)
    This has the potential to give duplicates if the count_id and num_id are the same across records.

    -cf
    You mean besides giving wrong results?
    Perhaps you mean
    Code:
    select id, desc_id, num_id, count_id
      from table
     where ( num_id, count_id ) in ( select num_id, min( count_id )
                                       from table
                                      group by num_id )

  5. #5
    Join Date
    Dec 2003
    Posts
    1,074
    I don't think it gives the wrong results. I apologize if there's another situation where this query is incorrect, but with the above data

    Code:
    SQL> select * from tab_id;
    
            ID DESC_     NUM_ID   COUNT_ID
    ---------- ----- ---------- ----------
             1 AAAA           1          2
             3 bbb            2          3
             7 sss            1          5
             4 ttt            6          7
             9 mmm            3          1
             8 llll           3          2
            10 dddd           4          6
            12 yyyyy          4          5
            15 rrrr           4          7
            16 nnnnn          4          3
    
    10 rows selected.
    
    SQL> select id, desc_id, num_id, count_id 
      2  from   tab_id t1
      3  where  count_id = (select min(t2.count_id) 
      4                     from   tab_id t2 
      5                     where  t1.num_id = t2.num_id);
    
            ID DESC_     NUM_ID   COUNT_ID
    ---------- ----- ---------- ----------
             1 AAAA           1          2
             3 bbb            2          3
             4 ttt            6          7
             9 mmm            3          1
            16 nnnnn          4          3
    -cf

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •