Interested to Buy Any Domain ? << Click Here >> for more details...
Hi,
Can anybody please explain me the flow of the below query.
I am not able to understand how this query works.
This query is for finding the Nth highest salary..
SELECT DISTINCT (a.sal)
FROM EMP A
WHERE &N = (SELECT COUNT (DISTINCT (b.sal)) FROM EMP B
WHERE a.sal<=b.sal);
If N = 2 then second highest salary will be the o/p.
If N = 3 then third highest salary will be the o/p.
and so on..
Thanks,
Nitin
- 5 Answers
- 15080 Views
- Deloitte, Ness Technologies, I also Faced
- E-Mail Answers
Answer Posted / satyajit patel
The Query above gives the nth highest distinct salary.
For explanation let a table emp with sal column like below
sal
1200
1300
1500
1200
1250
1700
1250
2000
See DISTINCT word is there in the query
So, you have to find the distinct sal first.
sal
1200
1300
1500
1250
1700
2000
Now see the condition a.sal<= b.sal
This condition compares a.sal and b.sal. The COUNT counts
how many times the a.sal is less than or equal to b.sal,
and gives this value as the output of sub query.
(N.B. comparing to same value means EQUAL SO count is 1).
This count is the value of N.
So after comparision the value of N for different salaries
are like
sal N
1200 6
1300 4
1500 3
1250 5
1700 2
2000 1
Now on querying when you give a value of N the
corresponding value of salary is selected.
Like if you are giving N=2 , then 1700 is displayed.
| Is This Answer Correct ? | 28 Yes | 0 No |
Post New Answer View All Answers
how to include comments in sql statements? : Sql dba
Which tcp/ip port does sql server run on? How can it be changed? : Sql dba
How delete all records from table in sql?
Explain the difference between drop and truncate commands in sql?
Where not exists in sql?
What is character functions?
What is string data type in sql?
What is the clause we need to add in function body to return variable?
What is meant by <> in sql?
How do I view an execution plan in sql?
how can we submit a form without a submit button? : Sql dba
What is string join?
What is a unique key?
what are all types of user defined functions? : Sql dba
What are schema-level triggers?
