Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2006
    Posts
    111

    Unanswered: happy new year and some questions

    hello, i haven't been around for a while and i wish everyone to have a great new year ahead.

    by the way, if anyone can help me solve the following problem say i have the following rows for a table:

    Code:
     
    PriKeyCol        Col1      Col2       Col3
    1                   1            3         20
    2                   2            6         512
    3                   1            2         23
    4                   4            9         429
    5                   3            8         92
    6                   4            7         35
    7                   2            9         1

    I would like to group by Col1 and then get the minimum of Col2 and the corresponding value in Col3 for those minimum so I get the following:

    Result Set:
    Code:
     
    1      2      23
    2      6      512
    3      8      92
    4      7      35
    Again, Many Thanks!
    Last edited by g11DB; 01-13-09 at 22:44.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT t.Col1
         , t.Col2
         , t.Col3
      FROM daTable AS t
    INNER
      JOIN ( SELECT Col1
                  , MIN(Col2) AS MIN_Col2
               FROM daTable
             GROUP
                 BY Col1 ) AS m
        ON m.Col1 = t.Col1
       AND m.MIN_Col2 = t.Col2
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2006
    Posts
    111

    thx and some suggestion

    thank you, it works, just note to other who might use your solution, i added a group by in the outer block in those cases where there are multiple rows for col1 but same value for col2. cheers!

Posting Permissions

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