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
- 13058 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 are local and global Indexes and where they are useful.
Why do we use joins in sql?
what are the differences between binary and varbinary? : Sql dba
What is the maximum number of columns in sql table?
How can we debug in PL/SQL?
How do I run a pl sql procedure in sql developer?
Write a program that shows the usage of while loop to calculate the average of user entered numbers and entry of more numbers are stopped by entering number 0?
Explain the savepoint statement.
How to select the Nth maximum salary from Table Emp in Oracle SQL Plus ?
What is the most restrictive isolation level? : Transact sql
What are the advantages of stored procedure?
Why indexing is needed?
Cite the differences between execution of triggers and stored procedures?
In pl/sql, what is bulk binding, and when/how would it help performance?
Why do we use function in pl sql?
