select max for more than one column?

Suppose i have the following table

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

I n开发者_JS百科eed 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

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

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

how about

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

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);

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

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 

How about...

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).