110

7
+2  Q:

## select max for more than one column?

Hello.. Suppose i have the following table

``````Students (StudentID, StudentName)
StudentMarks (StudentID, Mark)
``````

I need to select the student with the highest mark.. if there is more than one student have the same mark (and its the highest mark).. then select according to name order (alphabatically)

How can i do this? Thanks in advance

+1  A:
``````SELECT s.StudentName, sm.Mark
FROM Students s
INNER JOIN StudentMarks sm ON sm.StudentID = s.StudentID
WHERE sm.Mark = (SELECT MAX(Mark) FROM StudentMarks)
ORDER BY s.StudentName
``````
+1  A:

``````select *
from students s
inner join studentmarks m on m.studentid = s.studentid
where m.mark = (select Max(mark) from studentmarks)
order by s.StudentName
``````
+2  A:

I haven't tested this, but it sounds right

``````SELECT StudentID, StudentName
FROM Students
JOIN StudentMarks USING (StudentID)
WHERE Mark =
(SELECT MAX(Mark) FROM StudentMarks)
ORDER BY StudentName
``````
`StudentMarks.Mark` could probably be shortened to `Mark`, too.
A:

Other options

``````SELECT * FROM Students where StudentId in (SELECT StudentID FROM Mark Where Mark = Max(Max));
``````

or

``````SELECT s.* FROM Students where exists (SELECT StudentID FROM Mark m Where Mark = Max(Max) and m.StudentId = s.StudentId);
``````
A:

If your RDBMS supports analytic functions

``````WITH Ranked AS
(
SELECT RANK() OVER(ORDER BY  Mark DESC) AS Rnk,
Students.StudentID,
Students.StudentName,
Mark
FROM Students
JOIN StudentMarks ON Students.StudentID = StudentMarks.StudentID
)
SELECT StudentID,
StudentName,
Mark
FROM Ranked
WHERE Rnk=1
ORDER BY StudentName
``````

Or for the other interpretation of the spec...

``````WITH RowNumbered AS
(
SELECT ROW_NUMBER() OVER(ORDER BY  Mark DESC, StudentName ASC) AS Num,
Students.StudentID,
Students.StudentName,
Mark
FROM Students
JOIN StudentMarks ON Students.StudentID = StudentMarks.StudentID
)
SELECT StudentID,
StudentName,
Mark
FROM RowNumbered
WHERE Num=1
``````
A:

This just needs a simple join and to select the first record.. (by pre-ordering them according to specs)

``````SELECT TOP 1
S.StudentName, SM.Mark
FROM
Students S
INNER JOIN StudentMarks SM ON SM.studentID = S.StudentID
ORDER BY
SM.Mark DESC,
S.StudentName ASC
``````
I didn't downvote you but this syntax is SQL Server only (and would need `WITH TIES` to match the spec anyway) - Though actually that probably still wouldn't work as you have 2 cols in the ORDER BY.
@downvoter, I would appreciate a comment for the reason of the downvote..
@Actually having read the question again - I'm not sure about the second point I made. I'm not clear on the spec.
@Martin, maybe i am misreading the spec, but he mentions `select` according to name order. I read this as select the first one .. according to name order.. By the other answers i assume it is meant as select all of them and `sort` them according to name..
@Gaby - Yes. I suspect the downvote was maybe due to not using standard SQL. As the downvoter hasn't put forward an explanation though I've upvoted it.
A:

``````SELECT *
FROM Students
WHERE StudentID IN (SELECT StudentID
FROM StudentMarks
WHERE Mark = (SELECT MAX(Mark)
FROM StudentMarks))
ORDER BY StudentName
LIMIT 1;
``````

(expanding on Vash's answer to remove the nonstandard features from it).

From here I don't think `LIMIT` is in any standard http://troels.arvin.dk/db/rdbms/#select-limit
You may well be right; I was always taught `LIMIT TO n ROWS` but although that's standard hardly anyone implements it. From what I've seen on this site, hardly anyone uses it...