June 27, 2006 at 1:56 pm
select *
from bills a WHERE 3= ( select count(*)
from bills b WHERE b.amount > a.amount )
When I was searching for the nth highest salary I found the above query. But I didn't understan how it works and what happens when we say where 3=
Thanks.
June 27, 2006 at 2:12 pm
it is simply an equality operation A = 3 is the same as 3 = A and you are simply searching for those who have 3 counted records with higher values than the current one.
Cheers,
* Noel
June 27, 2006 at 2:28 pm
sql_2005_fan:
It means you are looking for an amount from "bills a" for which there are exactly 3 amounts which are higher in "bills b".
This means that you are looking for the 4th highest amount in "bills". The "3" would be "nth - 1".
June 27, 2006 at 2:41 pm
And if you really are a sql_2005_fan, you should not be using this and should be using the RANK/OVER functions in SQL 2005 as a more efficient alternative.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply