How to get the name of a the student who got max marks in each subject?

sql, sql-server, sql-server-2005, sql-server-2008, sql-server-2008-r2

Solution

You can use the ROW_NUMBER function to return only the "best" row per subject:

SQL Fiddle

MS SQL Server 2008 Schema Setup:

CREATE TABLE Student
    ([Name] varchar(1), [Subject] varchar(1), [Marks] int)
;

INSERT INTO Student
    ([Name], [Subject], [Marks])
VALUES
    ('a', 'M', 20),
    ('b', 'M', 25),
    ('c', 'M', 30),
    ('d', 'C', 44),
    ('e', 'C', 45),
    ('f', 'C', 46),
    ('g', 'H', 20)
;

Query 1:

SELECT Name, Subject, Marks
FROM(
  SELECT *, ROW_NUMBER()OVER(PARTITION BY Subject ORDER BY Marks DESC) rn
    FROM dbo.Student
)X
WHERE rn = 1

Results:

| NAME | SUBJECT | MARKS |
--------------------------
|    f |       C |    46 |
|    g |       H |    20 |
|    c |       M |    30 |

Problem

I have the following table ``` Name | Subject | Marks -------------------------- a M 20 b M 25 c M 30 d C 44 e C 45 f C 46 g H 20 ``` Here I have a "Student" table I want to get the Name of the student who got Max marks from each subject from the student table like the following OUTPUT. ``` Name | Subject | Marks c M 30 f c 46 g h 20 ```

Original source