Find maximum data in sql server

Question

i have this kind of table and find the maximum mark

STUDENT

|id | name  | mark |
|1  | john  | 56   |
|2  | sara  | 81   |
|3  | mattew| 65   |

the out suppose to be like this

|id | name  | mark |
|2  | sara  | 81   |

but i get this kind of output

|id | name  | mark |
|1  | john  | 81   |

i write this in sql

SELECT id,name,MAX(mark)
FROM student;
WHERE name IN
(SELECT name
FROM student);

how can i correct the sql?

Martin Smith · Accepted Answer · 2014-12-27 10:07:00Z

3

In SQL Server you can use

SELECT TOP 1 WITH TIES *
FROM student
WHERE mark IS NOT NULL
ORDER BY mark DESC

Though you could also use the logically equivalent standard SQL

SELECT *
FROM   student
WHERE  mark = (SELECT MAX(mark)
               FROM   student)

answered Dec 27, 2014 at 10:01

Martin Smith

457k97 gold badges777 silver badges887 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

DBname · Accepted Answer · 2014-12-29 06:42:28Z

0

You can use this simple query to get same result:

  SELECT TOP 1 WITH TIES ID,NAME,mark 
    FROM STUDENT
    ORDER BY MARK DESC

answered Dec 29, 2014 at 6:42

DBname

112 bronze badges