SQL - function that equavalant to MAX???

[horiyochi]

i may sound silly.. but that's that question that asked:

Search for The highest score

Description: There are 7 students in the class. Each of them have their own exam score. You are required to search for the name and score of whoever scores the highest.

Requirement: Provide a SQL statement to get the stated result.

Table Name: MyClass

Johnny 58
Albert 39
Steve 89
Jack 64
Mike 99
Lawrence 40
Andrew 99

Expected Result:
Mike 99
Andrew 99

Unacceptable Answers:

1. SELECT Name, MAX(Score) FROM MyClass

2. SELECT Name, MAX(Score) AS Score FROM MyClass

3. SELECT Name, Score FROM MyClass WHERE Score = 99

.. that's what in the question ... so just want to find out is there any function that can do it ?? other that MAX ?

cheers

[CardboardHammer]

I'm not going to give you a query that solves the problem, as this smells like homework. I'm not against giving inspiration, however.

You CAN still use MAX to get what you need (hint: subquery).

EDIT2: Extra hint: #3 is the query that's closest to what you need and will get you what you need when combined with hint above.

EDIT: removed bad idea after closer inspection of problem.

[horiyochi]

this is not a homework question, infact this is a question that ask in an interview.. and i am not planning to take that job... just want to find out is there anything equavalant to MAX

i know about subquery .. but what i want is to find the result without using the word MAX ... is it possible ...

anyone ?

[CardboardHammer]

TOP 1 used with ORDER BY DESC will get you the same result as MAX.
SELECT MAX(Score) FROM MyClass
is equivalent to
SELECT Top 1 Score FROM MyClass
ORDER BY Score DESC
So
SELECT Name, MAX(Score) FROM MyClass
is equivalent to
SELECT Name, (SELECT Top 1 Score FROM MyClass ORDER BY Score DESC) FROM MyClass

However, the rewritten version still doesn't give the results desired, and therefore falls into the "Unacceptable Answers" category.

You STILL have to use a subquery to solve the problem using a single query. Note that "Unacceptable Answers" does not forbid the use of MAX (the way you worded your question at the bottom of your OP implies that you think that it does); it just shows examples of what WON'T return the correct results. MAX is a much cleaner way write code to get a maximum value.

[CardboardHammer]

Note that if grouping is involved, there's more work involved to use TOP 1 and ORDER BY DESC subquery in place of MAX to give the same result set.

EDIT: "more work" both in writing the subquery and, unless the query optimizer is good enough to give you what you want in a more intelligent way, in executing the query.