N th Maximum Value
-
Hi,
Anybody expalin me how the below query is executing ?
I need the step by step execution process.
I dont know internally how it is displaying the 5 th maximum value.
table name : tes
output :
1 5000
2 6000
3 8000
4 12000
5 18000
6 100
query :
---------------------------------------------------------------------
SELECT * FROM tes e1 WHERE ((SELECT COUNT(DISTINCT (e2.salary))
FROM tes e2 WHERE e2.salary >= e1.salary)= 5)
-----------------------------------------------------------------------
Final Output :
1 5000
Regards
Karthik
karthik
-
To see how the query is executing, take a look at the execution plan. It's very hard to look at a query and say how it will execute
From a quick look, I can think of an alternative that may be quicker...
select top 1 * from tes
where salary not in (select top 4 salary from tes order by salary desc)
order by salary desc
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
This is using a sub-query. The sub-query "(SELECT COUNT(DISTINCT (e2.salary))
FROM tes e2 WHERE e2.salary >= e1.salary)" tallies up the number of distinct values that are greater or equal to each row in table e1. The implicit relationship here is a cross join, so it's implicitly matching ALL of the values of the table, against all of the values of the table all over again (thus the two aliases e1 and e2)
once the distinct count has its value, then the outer query simply looks for the record for which the computed value is 5. Notice that because you're counting DISTINCT values, you might get multiple records this way (which would be by design of the query).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again? -
Don't forget there is no ordering in the table. Just because you see data returned a certain way doesn't mean it exists that way or will always come back that way. If you want to order by the first field in the subquery, you need to add that
-
Also, which field is salary? It seems like you want to compare the first number and the second number at different places.
Maybe you can tell us what you are expecting or want to happen.
-
SELECT
MIN(Salary) FROM (SELECT TOP 5 Salary FROM Table1 ORDER BY Salary DESC) AS d
N 56°04'39.16"
E 12°55'05.25" -
This would be functionally the same as the initial query he had only if there are no duplicate values (in Salary). This query would return only one record, whereas the initial query would return everyone with the same salary. Also - the initial query returns everyone in the 5th tier of salaries, not who is #5 on the salary scale (very different questions being answered).
Now granted - this might in fact what he wants to achieve instead of the initial query, but these variants will not consistently return the same data at all.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again? -
Thanks Matt.
karthik
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply