Thread: Details of emp having the 788th highest salary

Question asked by quest visitor - krishna chaitanya

hi friends here's my question assume that we have about 1000 records in my emp table and i wish to know all the details of the employee having the 788 th highest salary i donot have any of the employee's details in hand (i mean i do not knowthe empno or the any specific column detail about the employee)can this be done using subqueries.AS it would take a chain of statements which is quiet impossible to be implemented manually.

hi,

the query for this is:

table name is : EMP

select * from emp X where &N = (select count(distinct(salary)) from emp Y where Y.salary >= X.salary);

here it will ask for the value of N for this enter 788 or what ever value u want.

one more way is there using analytical functions:

select * from (select ename, salary, dense_rank() over(order by salary desc) POS from emp) where POS = 788;

regards,

RSY

select * from emp order by sal asc limit 787,1;

Originally Posted by bvani

select * from emp order by sal asc limit 787,1;

this query is not working for me.

Its throwing error like: "SQL command not properly ended"

-----------------------------------------------------------------------
rem Purpose: Select the Nth highest value from a table
-----------------------------------------------------------------------

select level, max('col_name') from my_table
where level = '&n'
connect by prior ('col_name') > 'col_name')
group by level;

-- Example :
--
-- Given a table called emp with the following columns:
-- id number
-- name varchar2(20)
-- sal number
--
-- For the second highest salary:
--
-- select level, max(sal) from emp
-- where level=2
-- connect by prior sal > sal
-- group by level
--

Hi all

yes innila thats true the cmd

"select * from emp order by sal desc limit 789,1";

gives the 788th highest record in mysql.... sorry for the post..

try this

select * from emp
where (select count(distinct sal) from emp s
where s.sal>=m.sal)+1=788;

there are so many processes to do so

i am assuming that table has one column(in my case t column salary)

with p as
(
select rownum as num,salary as salary from t
order by rownum desc
)
select p.*from p
where num=&desired_salary

not tested

i just posted a same type solution to a near about same problem

http://www.geekinterview.com/talk/8603-print-last-3-rows-in-table.html

select salary from(select salary,rank() over(order by salary desc)r from bal) where r=788