How do we get maximum value from table without using Max,Min or Order by Clause in SQL Server 2005?

I have one table has Salary has 3 columns and I want to get maximum value from this table without using Max, Min or order by clause.

Data

Salary has three fields - ID,Name,Amount value of Amount - 100,70,80,400,300

I开发者_StackOverflow need to get value of 400 from this data.

I know Select max(Amount) from Salary, But i need without max,min or Order by clause.

Thanks in advance.

---

SELECT *
FROM salary s1
WHERE s1.amount > ALL (SELECT s2.amount 
                       FROM salary s2 
                       WHERE s2.id <> s1.id);

But I doubt that it will be faster than using max()

---

Given this is a theoretical question only and NOT AT ALL practical... here's my answer.

ALL is a cool solution for SQL Server (see notes below). For the rest, and any ANSI compliant RDBMS

SELECT *
FROM salary s1
WHERE not exists (SELECT *
                  FROM salary s2 
                  WHERE s2.id <> s1.id AND s2.Amount > s1.Amount);

It is a bit better than the ALL answer though, since it will show all the TIES if there are multiple with the Max(salary) whereas Martin's will not return any results if there are TIES at the MAX.

It may also run faster due to not exists quitting per s1 row for any matches found in the correlated subquery.

---

declare @amount int

SELECT @amount = CASE WHEN @amount > amount THEN @amount ELSE amount END 
FROM yourtable


select @amount

This is most definitely not an optimization though as (assuming an index on amount) MAX could stop scanning after the first result whereas this must scan all records.

It is still more efficient than this though

SELECT *
FROM salary s1
WHERE NOT EXISTS(SELECT * FROM salary s2 WHERE s2.amount  > s1.amount)

Which in turn is massively more efficient than ALL