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
- 14827 Views
- Deloitte, Ness Technologies, I also Faced
- E-Mail Answers
Answers were Sorted based on User's Feedback
Answer / 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 |
Answer / avi
Above Answer is correct With small modification that it is a
corelated sub query first it considers sal from a like a.sal
then it compares with all the sal in a sub query the result
will be the no. of counts.This count compares with N value
which matches will be the Nth sal.
| Is This Answer Correct ? | 5 Yes | 0 No |
Answer / ms75
Detailed answer available on following weblinks
-----------------------------------------------
1) How does this query work?
<http://www.sqlteam.com/article/find-nth-maximum-value-in-
sql-server>
2) How this query works?
<http://www.dbapool.com/forumthread/topic_4361.html>
| Is This Answer Correct ? | 1 Yes | 0 No |
Answer / mandar
HI, this querey is showing the output for the employees
salary. for the which employees salary is highest in that
table this query showing the output.
| Is This Answer Correct ? | 0 Yes | 1 No |
what are the differences between char and nchar? : Sql dba
What is the difference between join and union.
27 Answers 3i Infotech, ABC, Oracle, Polaris, TCS, XT Global,
What is magic table?
What are pl sql procedures?
how to create a new table by selecting rows from another table in mysql? : Sql dba
what is a record in a database ? : Sql dba
How can we find duplicate records in a table?
how to show all tables with 'mysql'? : Sql dba
How do I count rows in sql query?
What are pl/sql cursors?
i have xml source. xml source having lacks of records. i want extract/retrive xml source data using sql query. please tell me how to write query.
how to find 5th row ?
-
Oracle (3259) - SQL Server (4518)
- MS Access (429)
- MySQL (1402)
- Postgre (483)
- Sybase (267)
- DB Architecture (141)
- DB Administration (291)
- DB Development (113)
- SQL PLSQL (3330)
- MongoDB (502)
- IBM Informix (50)
- Neo4j (82)
- InfluxDB (0)
- Apache CouchDB (44)
- Firebird (5)
- Database Management (1411)
- Databases AllOther (288)
