| Author |
Topic |
|
gnanasekar
Starting Member
6 Posts |
 Posted - 2011-08-03 : 11:09:13
|
| Select * From Employees E1 Where (N-1) = (Select Count(Distinct(E2.Salary)) From Employees E2 Where E2.Salary > E1.Salary)HERE IS THE QUERY WAS FIND NTH SALARY FROM EMPLOYEES TABLE HOW IT WORKWHAT IS THE MEANING (N-1) |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2011-08-03 : 11:46:03
|
| Not sure. Looks like [N] may be a numerically based column in your [Employees] table.Does the statement work?Be One with the OptimizerTG |
 |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-08-03 : 11:48:43
|
| The query to the right side of the equal sign is counting how many distinct salaries are greater than any given salary. If you find that there are N-1 salaries that satisfy that condition, then that means that the given salary is the Nth. Hence the N-1 on the left side.You could perhaps do this more efficiently using row_number function if you are on SQL 2005 or higher. |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2011-08-03 : 12:05:25
|
quote:
Originally posted by sunitabeck
The query to the right side of the equal sign is counting how many distinct salaries are greater than any given salary. If you find that there are N-1 salaries that satisfy that condition, then that means that the given salary is the Nth. Hence the N-1 on the left side.You could perhaps do this more efficiently using row_number function if you are on SQL 2005 or higher.
I haven't seen that. This is MS SQL Server right? Can you post a simple example that I can run to see it in action?Be One with the OptimizerTG |
|
|
|
gnanasekar
Starting Member
6 Posts |
Posted - 2011-08-03 : 12:23:39
|
quote:
Originally posted by sunitabeck
The query to the right side of the equal sign is counting how many distinct salaries are greater than any given salary. If you find that there are N-1 salaries that satisfy that condition, then that means that the given salary is the Nth. Hence the N-1 on the left side.You could perhaps do this more efficiently using row_number function if you are on SQL 2005 or higher.
this query is to find 3rd max salary or nth salary this right way to say in interview or use row number. |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-08-03 : 12:28:47
|
quote:
Originally posted by TG
quote:
Originally posted by sunitabeck
The query to the right side of the equal sign is counting how many distinct salaries are greater than any given salary. If you find that there are N-1 salaries that satisfy that condition, then that means that the given salary is the Nth. Hence the N-1 on the left side.You could perhaps do this more efficiently using row_number function if you are on SQL 2005 or higher.
I haven't seen that. This is MS SQL Server right? Can you post a simple example that I can run to see it in action?Be One with the OptimizerTG
TG, I assumed that the N was a typo, and that it was rather a variable such as @N. Assuming that is the case, my interpretation is that it would work like in this example where I am trying to find the 4th highest ID.CREATE TABLE #tmp(id INT);INSERT INTO #tmp VALUES (10),(8),(7),(3),(1);DECLARE @n INT; SET @n = 4;SELECT * FROM #tmp e1WHERE ( SELECT COUNT(DISTINCT (id)) FROM #tmp e2 WHERE e2.id > e1.id)= @n-1;DROP TABLE #tmp; |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2011-08-08 : 09:30:50
|
quote:
Originally posted by TG
quote:
Originally posted by sunitabeck
The query to the right side of the equal sign is counting how many distinct salaries are greater than any given salary. If you find that there are N-1 salaries that satisfy that condition, then that means that the given salary is the Nth. Hence the N-1 on the left side.You could perhaps do this more efficiently using row_number function if you are on SQL 2005 or higher.
I haven't seen that. This is MS SQL Server right? Can you post a simple example that I can run to see it in action?Be One with the OptimizerTG
It is just like generating serial number based on the highest salary which is very ineffecient. You can find some similarities in methods 1 and 4. However I would suggest method 5http://beyondrelational.com/blogs/madhivanan/archive/2007/08/27/find-nth-maximum-value.aspxMadhivananFailing to plan is Planning to fail |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2011-08-08 : 16:50:29
|
Thanks Madi - I got all that. It was the original (OP's) post that didn't make sense to me. Didn't see how it could work at all:quote:
Select * From Employees E1 Where(N-1) = (Select Count(Distinct(E2.Salary)) From Employees E2 WhereE2.Salary > E1.Salary)
Sunitabeck rationalized the "N-1" as a typo.Be One with the OptimizerTG |
|
|
|
Ghanta
Yak Posting Veteran
96 Posts |
Posted - 2011-08-08 : 16:55:00
|
Or you can do CTE with TOP and Order By and then another SELECT WITH TOP...quote:
Originally posted by gnanasekar
quote:
Originally posted by sunitabeck
The query to the right side of the equal sign is counting how many distinct salaries are greater than any given salary. If you find that there are N-1 salaries that satisfy that condition, then that means that the given salary is the Nth. Hence the N-1 on the left side.You could perhaps do this more efficiently using row_number function if you are on SQL 2005 or higher.
this query is to find 3rd max salary or nth salary this right way to say in interview or use row number.
|
|
|
|
|
Explain the QUERY