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

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;

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.

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"

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;

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

akavitha

 Profile Answers by 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)

 

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'

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

sharmawithu

 Profile Answers by 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;

pradeep

• Sep 5th, 2011

 

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


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


3. UPDATE emp SET recno=45

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

Krishna Kumar Singh

• Oct 15th, 2011

 

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

Venkatesh

• Dec 9th, 2011

 

Nth Highest Salary in Oracle

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

Nth Lowest Salary in Oracle

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

srik

• Jan 12th, 2012

 

Code
SELECT * FROM


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


   WHERE Poporder = 2;

shivaraj

• May 9th, 2012

 

Code
SELECT lastname,salary FROM employee WHERE salary IN 


 (SELECT min(salary) FROM employee 


WHERE salary IN (SELECT TOP 2 SALARY  


FROM EMPLOYEE ORDER BY SALARY DESC))

abhikum.402

 Profile Answers by 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

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)

Srikanth

• May 22nd, 2014

 

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


FROM Employee Emp1


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


SELECT COUNT(DISTINCT(Emp2.Salary))


FROM Employee Emp2


WHERE Emp2.Salary > Emp1.Salary)

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;

neha lakhani

• Jul 20th, 2014

 

employe2 mains emp2 salary < compar to emp1 salary

ashish ubale

• Mar 10th, 2016

 

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