Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2006
    Posts
    18

    Unanswered: grouping question

    pn idc cnt
    -------------------- ----------- -----------
    a7447 1 3
    a7447 2 4
    a7447 3 5
    a7447 7 6
    a7447 8 7
    a7447 9 9
    a7447 11 1
    a7447 12 2
    a7447 15 8
    a7474 5 1
    a7474 6 2
    a7474 13 4
    a7474 14 5
    a7474 16 3

    from this data i need to group by pn field and get the idc for max cnt
    cnt could be repeated and for those possibilities it doesnt matter get multiple or just one of them doesnt matter.
    i hope this make sense

    the result for this list should look like

    pn idc cnt
    -------------------- ----------- -----------
    a7447 9 9
    a7474 14 5

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select pn 
         , idc 
         , cnt
      from daTable as T
     where cnt = 
           ( select max(cnt)
               from daTable
              where pn = T.pn )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2006
    Location
    UK
    Posts
    46
    Hi
    It should work as you described.


    Code:
    select t1.*
    from TheTable t1 inner join (select pn, 
                                        cnt = max(cnt)
                                 from TheTable
                                 group by pn) as t2 on t1.pn = t2.pn and
                                                       t1.cnt = t2.cnt
    order by t1.pn,
             t1.idc

    Regards
    Kris Zywczyk

  4. #4
    Join Date
    Jul 2006
    Posts
    18
    Thats what i did for this occation it does work but if i have multiple on the same cnt and if i need only one of them then we have a problem. But ill deal with that later on.
    Quote Originally Posted by r937
    Code:
    select pn
         , idc 
         , cnt
      from daTable as T
     where cnt = 
           ( select max(cnt)
               from daTable
              where pn = T.pn )

Posting Permissions

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