Now suppose i have 50 records of employee table.1) I want to find the person who is having 22nd highest salary.2)i want to delete a person with postion number 39 with comminsion < 1003) Update rec no 45records are not shown.how do i do it.

Showing Answers 1 - 24 of 24 Answers

Rajasekhar

  • Jun 12th, 2006
 

Use the below SQL statement

select employee_id,first_name,last_name from employees a where &n = (select count(salary) from employees b where a.salary <=b.salary)

Regards,

Rajasekhar.N

  Was this answer useful?  Yes

Sanjay

  • Jun 14th, 2006
 

select emp_id,emp_name, sal from employee a where &n = (select count(distinct(salary)) from employee b where a.sal <=b.sal)

&n=22 ( U will be asked to enter the number and then enter for which record u want to retrieve)

a.sal <=b.sal (this can be used to retrieve max record)

a.sal >=b.sal( this can be used to retrieve min record)

jyothi

  • Aug 15th, 2006
 

for 1 question the query is

select level,max(sal) from employees

 wherelevel=&level

connect by prior sal>sal

group by level; here u should mention level as22

2)delete from employees where positionnumber=39andcommision<100;

  Was this answer useful?  Yes

Manoj

  • Oct 23rd, 2006
 

For the 1st question

    Select * from emp where sal =   (Select * from (select sal from emp order by sal desc) group by sal having rownum=22)

Parvathi

  • Nov 9th, 2006
 

Hi,

For the 1st Question:
Ans:- Select a.sal from emp a where 29=(select count(distinct(b.sal)) from emp b Where a.sal<=b.sal)
For the 2nd Question:
Ans:-Delete from Employee where Positionnumber=39 and Commission<100;
For the 3rd Question:
And:-update employee set recno=45;

Thanks,

Parvathi.

  Was this answer useful?  Yes

Uday Kumar T.V.

  • Nov 9th, 2006
 

To find out the 22nd highest salary

use this query

select * from employee e1

where

        (select count(distinct(salary)) from employee e2

        where e2.salary>e1.salary) = (22-1)

"since there must only be 21 salaries above this"

  Was this answer useful?  Yes

vadivelan

  • Dec 2nd, 2006
 

First question:

select min(salary) from (select distinct salary from employee order by salary desc) where rownum<=22);

 or

select max(salary) from (select distinct salary from employee order by salary asc) where rownum<=22);

second question:

delete from employee where emp_id=39 and comm<100;

Third question

update table employee set recno=45;

  Was this answer useful?  Yes

Jayant Naikwade

  • Jun 30th, 2007
 

1) I want to find the person who is having 22nd highest salary.


SELECT * FROM (SELECT rownum r, A.* FROM
(SELECT
l.* FROM employee l ORDER BY l.empsal DESC) A)

WHERE B.R= '4' )

2) I want to delete a person with position
number 39 with commission < 100
DELETE FROM emp WHERE posno= 39 and comm < 100

3) Update
record number is 45
UPDATE emp WHERE recno = 45

  Was this answer useful?  Yes

akavitha

  • Feb 28th, 2008
 

 

Ans 1: (works only for db2 for oracle use rownum())

SELECT EMP_NAME,SAL FROM (SELECT EMP_NAME,SAL,ROWNUMBER() OVER (ORDER BY SAL desC) AS RN FROM EMP) A
WHERE
A.RN = 22

Ans 2 : Question is not clear.

delete from emp where positionnumber=39 and commission<100;

 

Ans 3: Update rec no 45

UPDATE EMP  SET EMP_NAME='XYZ'
WHERE
EMP_NAME=(SELECT EMP_NAME FROM (SELECT EMP_NAME,SAL,ROWNUMBER() OVER (ORDER BY EMP_NO ASC) AS RN FROM EMP) A
WHERE
A.RN = 45)

 





  Was this answer useful?  Yes

RamThiraviyam

  • Aug 11th, 2011
 

1) I want to find the person who is having 22nd highest salary.

IN_MYSQL
=======

SELECT pame from table_name ORDER BY DESC LIMIT 22,1

2)i want to delete a person with postion number 39 with comminsion < 100


DELETE * FROM tbl_name WHERE positinNumber='19' AND commision<100

3) Update rec no 45

UPDATE table_name SET name='ram' ,salary='45000' WHERE rec_no='45'

  Was this answer useful?  Yes

Sundar

  • Aug 23rd, 2011
 

Select Top 1 Sal from SALARY Where Sal IN
(Select distinct Top 22 Sal from SALARY order by Sal Desc) order by Sal

  Was this answer useful?  Yes

sharmawithu

  • Aug 28th, 2011
 

For the first question:

select * from emp e where 22=(select count(distinct sal) from emp b where b.emp<= e.emp);


Second question:

delete * from emp where positionnumber = 39 and comm < 1000;

Update emp set rec=45;

  Was this answer useful?  Yes

pradeep

  • Sep 5th, 2011
 

Code
  1. 1. SELECT Sal FROM emp e WHERE 22=(SELECT COUNT(DISTINCT Sal) FROM emp e1 WHERE e.Sal<=e1.Sal)

  2. 2.DELETE FROM emp WHERE positionnumber=39 AND commission < 100

  3. 3. UPDATE emp SET recno=45

  Was this answer useful?  Yes

Balu

  • Oct 4th, 2011
 

SELECT TOP 1 * FROM Emp WHERE sal IN
(SELECT DISTINCT TOP 12 sal FROM Orders ORDER BY sal DESC) ORDER BY sal

  Was this answer useful?  Yes

Krishna Kumar Singh

  • Oct 15th, 2011
 

1-Select salery from emp where rownum=22 order by desc;

  Was this answer useful?  Yes

Venkatesh

  • Dec 9th, 2011
 

Nth Highest Salary in Oracle

Code
  1. SELECT max(Salary) FROM ( SELECT Salary FROM Employee WHERE rownum <=n ORDER BY Salary DESC)


Nth Lowest Salary in Oracle

Code
  1. SELECT min(Salary) FROM ( SELECT Salary FROM Employee WHERE rownum <=n ORDER BY Salary )

  Was this answer useful?  Yes

srik

  • Jan 12th, 2012
 

Code
  1. SELECT * FROM

  2.    (SELECT population,ROW_NUMBER() OVER(ORDER BY population)Poporder FROM india)

  3.    WHERE Poporder = 2;

  Was this answer useful?  Yes

shivaraj

  • May 9th, 2012
 

Code
  1. SELECT lastname,salary FROM employee WHERE salary IN

  2.  (SELECT min(salary) FROM employee

  3. WHERE salary IN (SELECT TOP 2 SALARY  

  4. FROM EMPLOYEE ORDER BY SALARY DESC))

  Was this answer useful?  Yes

abhikum.402

  • Aug 13th, 2012
 

Select * from (select *,Dense_RANK()over (order by salary column) SalayRank from tablename)A
where SalayRank=22

Ex:Select * from (select *,Dense_RANK()over (order by Emp_sal) SalayRank from Emp_a)A
where SalayRank=22

By this query you can achieve the first part of your question

  Was this answer useful?  Yes

Mahesh

  • Sep 25th, 2012
 

For 22nd highest salary....

select * min(salary) from employee
where salary in(select distinct top 22 salary from employee order by salary desc)

  Was this answer useful?  Yes

Srikanth

  • May 22nd, 2014
 

Code
  1. SELECT * /*This is the outer query part */

  2. FROM Employee Emp1

  3. WHERE (N-1) = ( /* Subquery starts here */

  4. SELECT COUNT(DISTINCT(Emp2.Salary))

  5. FROM Employee Emp2

  6. WHERE Emp2.Salary > Emp1.Salary)

  Was this answer useful?  Yes

Akhillesh kumar

  • Jun 15th, 2014
 

Ans 1> I want to find the person who is having 22nd highest salary.

SELECT * FROM EMP WHERE SAL IN (SELECT MAX(SAL) FROM EMP WHERE LEVEL=22
CONNECT BY PRIOR SAL>SAL GROUP BY LEVEL;

  Was this answer useful?  Yes

neha lakhani

  • Jul 20th, 2014
 

employe2 mains emp2 salary < compar to emp1 salary

  Was this answer useful?  Yes

ashish ubale

  • Mar 10th, 2016
 

Parvathi, last query(update) will set all recno to 45. Because it does not have where condition.

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

 

Related Answered Questions

 

Related Open Questions