How to get the name of a the student who got max m

问题:

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

回答1:

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 |

回答2:

You can use other functions and cte also to get the result..

eg : 1

select B.Name,
       A.Subject,
       B.Marks
from ( select Subject,
              max(Marks) as High_Marks
         from Student
       group by Subject
     ) a
  join Student b
    on a.subject = b.subject
   and a.high_Marks = b.Marks

Eg : 2 : use of cte and dense_rank function

;WITH cte

AS

(

SELECT

   [Name],

   [Subject],

   [Marks],

   dense_rank() over(partition BY [Subject] order by [Marks] DESC) AS Rank

FROM Student

)

SELECT * FROM cte WHERE Rank = 1;

回答3:

SQL> with cte as
  2  (
  3  select name, subject, marks, dense_rank() over (partition by subject order
by marks desc) rnk
  4  from student)
  5  select name, subject, marks
  6  from cte
  7  where rnk=1;

N S      MARKS
- - ----------
f c         46
c m         30

SQL>

回答4:

SELECT Max(Name) as Name, Subject, Max(Marks) as Marks
FROM Student
group by Subject

回答5:

This Basic Query should work for your req.

SELECT Name, Subject, Max(Marks)
FROM Student
GROUP by Subject;

Tried in SQLFiddle

Note: Used SQLite for check