Details of emp having the 788th highest salary
[B]Question asked by quest visitor - krishna chaitanya[/B]
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.
Re: Details of emp having the 788th highest salary
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
Re: Details of emp having the 788th highest salary
select * from emp order by sal asc limit 787,1;
Re: Details of emp having the 788th highest salary
-----------------------------------------------------------------------
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
--
Re: Details of emp having the 788th highest salary
[QUOTE=bvani;9205]select * from emp order by sal asc limit 787,1;[/QUOTE]
this query is not working for me.
Its throwing error like: "SQL command not properly ended"
Re: Details of emp having the 788th highest salary
[QUOTE]
this query is not working for me.
Its throwing error like: "SQL command not properly ended"
[/QUOTE]
I believe this command wont work in Oracle database.
If im right, i think this command is compatible in MySQL database. Probably bvani can clarify regarding this.
Re: Details of emp having the 788th highest salary
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..
Re: Details of emp having the 788th highest salary
try this
select * from emp
where (select count(distinct sal) from emp s
where s.sal>=m.sal)+1=788;
Re: Details of emp having the 788th highest salary
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
[URL="http://www.geekinterview.com/talk/newreply.php?do=newreply&noquote=1&p=24758"]
http://www.geekinterview.com/talk/8603-print-last-3-rows-in-table.html[/URL]
Re: Details of emp having the 788th highest salary
select salary from(select salary,rank() over(order by salary desc)r from bal) where r=788