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
精彩评论