CODEWITHSUNDEEP
sqlsql-server
user383698
user383698

Reputation: 85

SQL Max on Group By

I'm stuck at a very simple t-sql query. Please help !

Following is my result set :

Percentage---FirstName---SessionId

34-----------ABC---------222
67-----------ABC---------333
11-----------ABC---------444
83-----------XYZ---------555
23-----------XYZ---------666
64-----------XYZ---------777

From above, I want records of each user with maximum percentage e.g.

Percentage---FirstName---SessionId
67-----------ABC---------333
83-----------XYZ---------555

I can't seem to do that and I'm in hurry. Please help at the earliest. Any help would be greatly appreciated.

Thanks.

Upvotes: 1

Views: 14678

Answers (2)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239814

If you need to deal with ties, then you might want to use the windowing functions (assuming you're on SQL Server 2005 or later):

SELECT
    *
FROM
    (SELECT mytbl.*,RANK() OVER (PARTITION BY FirstName ORDER BY Percentage desc) as rn) t
WHERE
    t.rn = 1

So if there are two rows with the same percentage, they'll both be returned. If you only want one result, then you can add additional "tie-breaker" columns to the ORDER BY clause, or switch from RANK() to ROW_NUMBER().

Upvotes: 6

zerkms
zerkms

Reputation: 255115

  SELECT MAX(Percentage) AS Percentage,
         FirstName
    FROM mytbl
GROUP BY FirstName

Or if you need session id:

    SELECT mytbl.*
      FROM mytbl
INNER JOIN (SELECT MAX(Percentage) AS Percentage,
                   FirstName
              FROM mytbl
          GROUP BY FirstName) x ON x.Percentage = mytbl.Percentage
                               AND x.FirstName = mytbl.FirstName

Upvotes: 8

Related Questions