Results 1 to 2 of 2
  1. #1
    Join Date
    May 2006
    Posts
    1

    Question Unanswered: the rows with max Val_1 for each Name (was "SQL Querry help")

    I have table like this (Using SQL Server) :


    TempTbl:

    ID Name Val_1 Val_2
    ===============================
    1 A 10 adf
    2 B 12 dasfasd
    3 C 5 fgsdfg
    4 A 21 dsfg
    5 C 13 sdbs



    I want to result a table showing all col with max value in Val_1 for each Name. i.e.

    ID Name Val_1 Val_2
    ===============================
    2 B 12 dasfasd
    4 A 21 dsfg
    5 C 13 sdbs



    I can only think of this SQL:

    SELECT * FROM TempTbl t1, (SELECT Name, max(val_1) FROM TempTbl Group By Name) t2
    WHERE t1.Name = t2.Name AND t1.Val_1 = t2.Val_1



    I would like to ask is there any better way for searching this result rather than
    joinning the itself twice (becuase the TempTbl is very big).


    Many Thanks.!!!!

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

Posting Permissions

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