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
- 14908 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
Is id a reserved word in sql?
Explain the commit statement.
What is rename in sql?
How to write a query to show the details of a student from students table whose
what is data control language? : Sql dba
What is a pragma statement?
What is embedded sql what are its advantages?
What are sql functions? Describe in brief different types of sql functions?
what is a join? : Sql dba
how to shut down the server with 'mysqladmin'? : Sql dba
how many ways we can we find the current date using mysql? : Sql dba
Why is normalization important?
What is a pdo connection?
Are views faster than queries?
How do you take the union of two tables in sql?
