Follow Our FB Page << CircleMedia.in >> for Daily Laughter. We Post Funny, Viral, Comedy Videos, Memes, Vines...


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

Answers were Sorted based on User's Feedback



Hi, Can anybody please explain me the flow of the below query. I am not able to understand how t..

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

Hi, Can anybody please explain me the flow of the below query. I am not able to understand how t..

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

Hi, Can anybody please explain me the flow of the below query. I am not able to understand how t..

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

Hi, Can anybody please explain me the flow of the below query. I am not able to understand how t..

Answer / nitin

Thank you.

Is This Answer Correct ?    2 Yes 1 No

Hi, Can anybody please explain me the flow of the below query. I am not able to understand how t..

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

Post New Answer



More SQL PLSQL Interview Questions

I have a tablle like this. cust acc --------------- a 1 b 2|3 c 4|5|6 I Want below o/p: cust acc ----------- a 1 b 2 b 3 c 4 c 5 c 6 Please any one can you have any ideas share me. I have urgent requirement.

3 Answers   Cap Gemini, MTS,


Is it possible to update views?

0 Answers  


Is oracel sql developer written in java?

0 Answers  


What is a left join?

0 Answers  


Difference between SUBSTR and INSTR?

3 Answers  






write an sql query to get third maximum salary of an employee from a table named employee_table. : Sql dba

0 Answers  


How do I order by ascending in sql?

0 Answers  


how to create table with in the procedure or function?

2 Answers   Genpact, HCL,


How to find the count of letter "L" in HELLO

6 Answers   HP,


i have 2 table table one 4 columns respective values a1 7,a2 6,a3 8 ,a4 12 & table two 4 colums respective values a1 7,a2 6,a3 8,a4 15.if table one & table two 3 colums same then 4th column values 1)Qes diff >5 then print 5 * diff value 2)Que diff <5 print 5

1 Answers  


what is the stuff function and how does it differ from the replace function? : Sql dba

0 Answers  


What is dml statement?

0 Answers  






Categories