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
- 14811 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
What is the purpose of the partition table?
How can you fetch common records from two tables?
How many indexes can be created on a table in sql?
What is cursor and why it is required?
Can we use the cursor's to create the collection in PL/SQL?
Is there a pl/sql pragma similar to deterministic, but for the scope of one single sql select?
What is where clause in sql?
Is sql better than excel?
What is normalisation and its types?
How do I quit sql?
Why query optimization is needed?
How do I sort a table in sql?
Can we insert in view in sql?
How do sql triggers work?
What is the process of debugging?
