Results 1 to 10 of 10

Thread: Details of emp having the 788th highest salary

  1. #1
    Geek_Guest
    Guest

    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.


  2. #2
    Contributing Member
    Join Date
    Jan 2007
    Answers
    31

    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


  3. #3
    Expert Member
    Join Date
    Apr 2006
    Answers
    124

    Re: Details of emp having the 788th highest salary

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


  4. #4
    Junior Member
    Join Date
    Mar 2007
    Answers
    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
    --


  5. #5
    Expert Member
    Join Date
    Dec 2006
    Answers
    204

    Re: Details of emp having the 788th highest salary

    Quote Originally Posted by bvani View Post
    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"


  6. #6
    Expert Member
    Join Date
    Sep 2006
    Answers
    130

    Re: Details of emp having the 788th highest salary

    this query is not working for me.
    Its throwing error like: "SQL command not properly ended"
    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.

    *** Innila ***

  7. #7
    Expert Member
    Join Date
    Apr 2006
    Answers
    124

    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..


  8. #8
    Junior Member
    Join Date
    Jan 2008
    Answers
    24

    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;


  9. #9
    Contributing Member
    Join Date
    Apr 2006
    Answers
    46

    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

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



  10. #10
    Junior Member
    Join Date
    Oct 2006
    Answers
    1

    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


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
About us
Applying for a job can be a stressful and frustrating experience, especially for someone who has never done it before. Considering that you are competing for the position with a at least a dozen other applicants, it is imperative that you thoroughly prepare for the job interview, in order to stand a good chance of getting hired. That's where GeekInterview can help.
Interact