A table namely emp has two fields, empno and salary. There are total 20 tuples(records ) in the table. Salary can be same for more than one employee. I want to know the query which can give the fifth highest salary along with empno. Kindly let me know the exact query. Thanks in Advance

Re: Help me to solve this query And it appears there is an instructor who gave you this assignment to reinforce the classroom material. Having one of us simply give you the answer teaches you nothing. Post the work you've done to solve this problem and one of us can assist you.

Re: Help me to solve this query I am in professional world so no classroom assignment to be forced on me . I am using rownum pseudo column for this query but I am getting top 5 salaries not the 5th highest salary. What will be the query for it ?

Re: Help me to solve this query Hi sandip, please post the create table, sample insert statements and the query which you have written which is not giving the desired result. I guarantee you that we will solve your problem.

Re: Help me to solve this query You can't use ROWNUM to solve this query if you have multiple employees with the same salary; the EMP table is a demonstration table and is used for classrom study, hence my comment. As Sadik just replied post your create table statement, insert statements for sample data and the query you've written to solve this and someone will help you.

Re: Help me to solve this query Sorry for being a bit late in my reply. I have used the code :- create table emp(empno varchar2(30) primary key not null, salary number(10,2)); insert into emp values('01',20000); insert into emp values('02',20000); insert into emp values('03',10000); insert into emp values('04',1239); insert into emp values('05',1211); insert into emp values('06',18900); insert into emp values('07',26000); insert into emp values('08',12991); insert into emp values('09',9812); insert into emp values('10',6701); insert into emp values('11',9012); insert into emp values ('12',8800); insert into emp values('13',19000); I am required to find out the fifth highest salary for it using SQL select statements. Kindly let me know asap .

Re: Help me to solve this query Where is the query you've written to try to solve this? An employer would not ask an employee to query the EMP demonstration table so I truly feel that this is homework or a classroom assignment.

Re: Help me to solve this query I have written this Select Rownum as position, salary from (Select empno,salary from emp order by salary desc) where rownum < = 5 I am able to get top 5 earners in this way, But I want to know empno,salary of the 5th highest employee .Kindly mention the changes needed to be done in this query to get the desired Output.

Re: Help me to solve this query Hi Sandip, As Zargon said we can write query in a minute and you can get answer. but you must put your hands on. I can give u the hint, if you are using rownum <= 5, then you will get 1,2,3,4 and 5. thats all , correct it.

Re: Help me to solve this query You get the top 4 'earners' that way since two employees have the same salary: Code (SQL): SQL> -- SQL> -- Create table SQL> -- SQL> CREATE TABLE emp(empno varchar2(30) PRIMARY KEY NOT NULL, salary NUMBER(10,2)); TABLE created. SQL> SQL> -- SQL> -- Insert data the long and tedious way SQL> -- SQL> INSERT INTO emp VALUES('01',20000); 1 ROW created. SQL> INSERT INTO emp VALUES('02',20000); 1 ROW created. SQL> INSERT INTO emp VALUES('03',10000); 1 ROW created. SQL> INSERT INTO emp VALUES('04',1239); 1 ROW created. SQL> INSERT INTO emp VALUES('05',1211); 1 ROW created. SQL> INSERT INTO emp VALUES('06',18900); 1 ROW created. SQL> INSERT INTO emp VALUES('07',26000); 1 ROW created. SQL> INSERT INTO emp VALUES('08',12991); 1 ROW created. SQL> INSERT INTO emp VALUES('09',9812); 1 ROW created. SQL> INSERT INTO emp VALUES('10',6701); 1 ROW created. SQL> INSERT INTO emp VALUES('11',9012); 1 ROW created. SQL> INSERT INTO emp VALUES ('12',8800); 1 ROW created. SQL> INSERT INTO emp VALUES('13',19000); 1 ROW created. SQL> SQL> commit; Commit complete. SQL> SQL> -- SQL> -- Wrong query SQL> -- SQL> -- Gives top four salaries in the table SQL> -- SQL> SELECT empno, salary 2 FROM 3 (SELECT empno, salary FROM emp ORDER BY salary DESC) 4 WHERE rownum <=5; EMPNO SALARY ------------------------------ ---------- 07 26000 01 20000 02 20000 13 19000 06 18900 SQL> Here is one way to get the correct answer: Code (SQL): SQL> -- SQL> -- Gives correct result but is long and convoluted SQL> -- SQL> SELECT empno, salary 2 FROM emp 3 WHERE salary = (SELECT salary FROM 4 (SELECT salary, rownum rn FROM 5 (SELECT DISTINCT salary 6 FROM emp 7 ORDER BY salary DESC) 8 ) 9 WHERE rn = 5) 10 / EMPNO SALARY ------------------------------ ---------- 08 12991 SQL> And a better way: Code (SQL): SQL> -- SQL> -- A better way to get the correct answer SQL> -- SQL> SELECT empno, salary 2 FROM 3 (SELECT empno, salary, dense_rank() OVER (ORDER BY salary DESC) rk 4 FROM emp) 5 WHERE rk = 5 6 / EMPNO SALARY ------------------------------ ---------- 08 12991 SQL>

Re: Help me to solve this query We can get this way using correlated sub query: SELECT A.EMPNO, A.SALARY FROM EMP A WHERE &N = (SELECT COUNT(DISTINCT SALARY) FROM EMP B WHERE A.SALARY <= B.SALARY); provide any value for N. suppose you want 5th salary , give N = 5. assume that there is deptno and you want top Nth salary of each department SELECT A.EMPNO, A.SALARY FROM EMP A WHERE &N = (SELECT COUNT(DISTINCT SALARY) FROM EMP B WHERE A.SAL <= B.SAL AND A.DEPTNO = B.DEPTNO);

Re: Help me to solve this query Your first query, as written, does not work: Code (SQL): SQL> SELECT A.EMPNO, A.SALARY FROM EMP A WHERE &N = (SELECT COUNT(DISTINCT SALARY) FROM EMP B WHERE A.SAL <= B.SAL); Enter VALUE FOR n: 5 OLD 1: SELECT A.EMPNO, A.SALARY FROM EMP A WHERE &N = (SELECT COUNT(DISTINCT SALARY) FROM EMP B WHERE A.SAL <= B.SAL) NEW 1: SELECT A.EMPNO, A.SALARY FROM EMP A WHERE 5 = (SELECT COUNT(DISTINCT SALARY) FROM EMP B WHERE A.SAL <= B.SAL) SELECT A.EMPNO, A.SALARY FROM EMP A WHERE 5 = (SELECT COUNT(DISTINCT SALARY) FROM EMP B WHERE A.SAL <= B.SAL) * ERROR at line 1: ORA-00904: "B"."SAL": invalid identifier Modifying the query to reference the correct column names results in success at the expense of two complete table scans on EMP: Code (SQL): SQL> SELECT A.EMPNO, A.SALARY FROM EMP A WHERE &N = (SELECT COUNT(DISTINCT SALAR Y) FROM EMP B WHERE A.SALARY <= B.SALARY); Enter VALUE FOR n: 5 OLD 1: SELECT A.EMPNO, A.SALARY FROM EMP A WHERE &N = (SELECT COUNT(DISTINCT S ALARY) FROM EMP B WHERE A.SALARY <= B.SALARY) NEW 1: SELECT A.EMPNO, A.SALARY FROM EMP A WHERE 5 = (SELECT COUNT(DISTINCT SA LARY) FROM EMP B WHERE A.SALARY <= B.SALARY) EMPNO SALARY ------------------------------ ---------- 08 12991 Execution Plan ---------------------------------------------------------- Plan hash VALUE: 2162172199 ---------------------------------------------------------------------------- | Id | Operation | Name | ROWS | Bytes | Cost (%CPU)| TIME | ---------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 30 | 9 (12)| 00:00:01 | |* 1 | FILTER | | | | | | | 2 | TABLE ACCESS FULL | EMP | 13 | 390 | 4 (0)| 00:00:01 | | 3 | SORT GROUP BY | | 1 | 13 | | | |* 4 | TABLE ACCESS FULL| EMP | 1 | 13 | 4 (0)| 00:00:01 | ---------------------------------------------------------------------------- Predicate Information (IDENTIFIED BY operation id): --------------------------------------------------- 1 - FILTER( (SELECT COUNT(DISTINCT "SALARY") FROM "EMP" "B" WHERE "B"."SALARY">=:B1)=5) 4 - FILTER("B"."SALARY">=:B1) Note ----- - dynamic sampling used FOR this statement Statistics ---------------------------------------------------------- 9 recursive calls 0 db block gets 108 consistent gets 0 physical reads 0 redo SIZE 581 bytes sent via SQL*Net TO client 492 bytes received via SQL*Net FROM client 2 SQL*Net roundtrips TO/FROM client 12 sorts (memory) 0 sorts (disk) 1 ROWS processed SQL> It's an interesting solution.

Re: Help me to solve this query we should avoid use of correlated sub query in real time as much as possible, why because it costs so much

Re: Help me to solve this query Here's the long way to do it: select b.empno,a.salary from (select b.empno,a.salary from (select salary from ( select salary from emp group by salary order by salary desc ) where rownum < = :K--input parameter order by salary asc) a,emp b where 1=1 and rownum = 1 and a.salary = b.salary )a,emp b where a.salary = b.salary

Re: Help me to solve this query I am an ameture SQL user but you can try this Code (SQL): SELECT empno,salary FROM emp_table WHERE salary = ( SELECT salary FROM emp_table WHERE rownum<6 ORDER BY salary DESC -- this will give top 5 salary minus SELECT salary FROM emp_table WHERE rownum<5 ORDER BY salary DESC -- this minus will remove te top 4 from the list ) Remember, if your table is huge, it will give performence proble. but for small tables (less than 100000 records), this should work fine...

Re: Help me to solve this query In this particular case your first subquery returns the top 4 salaries as one of those occurs for two different employees. Executing the minus subtracts the top 3 from that leaving you with the fourth highest salary. Correct solutions have been posted which take this salary duplication into account.