We recently updated our
Terms and Conditions for TechRepublic Premium.
By clicking continue, you agree to these updated terms.
Invalid email/username and password combination supplied.
An email has been sent to you with instructions on how to reset your password.
Welcome to TechRepublic!
All fields are required. Username must be unique. Password must be a minimum of 6 characters and have any 3 of the 4 items: a number (0 through 9), a special character (such as !, $, #, %), an uppercase character (A through Z) or a lowercase (a through z) character (no spaces).
Due to scheduled maintenance, the ability to log in to your account and make purchases will be disabled December 1 - 2.
Select TOP 1 e1.sal
From emp e1
where e1.sal < (Select Max(e2.sal) From emp e2)
order by e1.sal DESC.
The sub-query will select the max first(1 value), then use it as the criteria for the main query.
This will work in both Access and SQL Server 6.5+, but not in Oracle. In Oracle there's a ROWID property (I think that's what it's called, it's been a while) that will let you limit the number of records returned by using it in the Where clause (ROWID = 1).
You also might try using Max(sal) in the main query instead of TOP 1, I haven't tried it, but it should work.