CODEWITHSUNDEEP
sqlsql-server
GokhanISIK
GokhanISIK

Reputation: 41

In sql To use ALL command instead of MAX command

I want to Find sids with the highest rating with all command.

I Find it with max command by writing 

select S.sid,S.sname 
from Sailors S 
where S.rating = (select max(S1.rating) from Sailors S1)

But also I want to find it by using all command so I write it by writing

select S.sid,S.sname 
from Sailors S 
where  S.rating > all (select S.rating from Sailors S)

But it Doesn't give the same result.

What is My error? How can I regulate it?

Upvotes: 2

Views: 1178

Answers (2)

Chris Hayes
Chris Hayes

Reputation: 12050

You simply need to modify your query to the following:

select S.sid,S.sname 
from Sailors S 
where  S.rating >= all (select S.rating from Sailors S)

While Mahmoud is correct in part about what is wrong with your condition currently, adding the possibility of equality gives you identical behavior to the MAX query, since each element in the set must be greater or equal to itself. (Mahmoud's point that no element can be greater than itself is accurate.)

See this SQLfiddle that Mahmoud kindly provided.

As ypercube points out in the comments, this method does not work if any values in the column are NULL. It should still work fine if you modify the subquery to specify WHERE rating IS NOT NULL, or for NOT NULL columns.

Upvotes: 6

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115630

In SQL-Server, you can also simply use ORDER BY combined with TOP (1) and WITH TIES specification:

SELECT TOP (1) WITH TIES
    S.sid, S.sname 
FROM Sailors S 
ORDER BY S.rating DESC ;

Upvotes: 2

Related Questions