Interested to Buy Any Domain ? << Click Here >> for more details...
how to check the 3rd max salary from an employee table? One
of the queries used is as follows:
select sal from emp a where 3=(select
count(distinct(sal)) from emp b
where a.sal<=b.sal).
Here in the sub query "select
count(distinct(sal)) from emp b
where a.sal<=b.sal" or
"select count(distinct(sal)) from emp b
where a.sal=b.sal" should reveal the same number of rows is
in't it? Can any one here please explain me how is this
query working perfectly.
However, there is another query to get the 3rd highest of
salaries of employees that logic I can understand. Pls find
the query below.
"select min(salary) from emp where salary in(select
distinct
top 3 salary from
emp order by salary desc)"
Please explain me how
"select sal from emp a where 3=(select
count(distinct(sal)) from emp b
where a.sal<=b.sal)" works
source:http://www.allinterview.com/showanswers/33264.html.
Thanks in advance
Regards,
Karthik.
- 4 Answers
- 8486 Views
- I also Faced
- E-Mail Answers
Answer Posted / masthan
SELECT MAX(SAL) FROM
EMP WHERE SAL<(SELECT MAX(SAL) FROM EMP WHERE SAL<(SELECT MAX(SAL) FROM EMP))
| Is This Answer Correct ? | 3 Yes | 2 No |
Post New Answer View All Answers
Differences between Oracle 9i and 10g (Probably in terms of SQL and PL/SQL)?
Can we use the cursor's to create the collection in PL/SQL?
What is the difference between local and global temporary table?
What is a field in a database?
Why join is faster than subquery?
Give the order of sql select?
What is record in pl sql?
what does the t-sql command ident_current does? : Transact sql
What is the difference between nested table and varray?
What are the various levels of constraints?
what is the use of anchoring object? what r the difference between anchoring object & enclosing object? can any one tell me all the details?
What is foreign key sql?
how to convert character strings to numeric values? : Sql dba
what is 'trigger' in sql? : Sql dba
How do you modify a column in sql?
