Results 1 to 5 of 5

Thread: Retrieve data from emp table

  1. #1
    Junior Member
    Join Date
    Feb 2007
    Answers
    1

    Retrieve data from emp table

    Hi All,

    Can anybody help me in solving this simple query.
    I have one emp table where empname and hiredate are few columns.
    I want to retrieve in which year most of the employess are joined.
    So in my table in the year 1981 more employess are joined.
    Can anybody help how to extract this year.

    Regards

    Sampurna


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

    Re: Retrieve data from emp table

    select to_char(hiredate,YYYY),max(count(to_char(hiredate,YYYY)))
    from emp
    groupby to_char(hiredate,YYYY),;

    This will retrieves the year in which maximum employees joined and how many joined in that year.


  3. #3
    Contributing Member
    Join Date
    Apr 2007
    Answers
    46

    Re: Retrieve data from emp table

    Hi,

    Try this:-

    select hire_dt,emp_name from (select rownum rw, emp_name,hire_dt from(select * from emp order by hire_dt desc)) where rw =&n;

    here u will give value of n as position u want.eg.
    first highest year have n=1,
    sec will have n=2,
    third n=3 and so on...........

    select hire_dt,emp_name from (select rownum rw, emp_name,hire_dt from(select * from emp order by hire_dt desc)) where rw =1;

    Thanks
    Reeta


  4. #4
    Expert Member
    Join Date
    Nov 2006
    Answers
    518

    Re: Retrieve data from emp table

    This WILL work

    SELECT TOP 1 Count(*) AS C1, Year(hiredate) AS C2 FROM emp GROUP BY Year(hiredate) ORDER BY C1 DESC;

    Lack of WILL POWER has caused more failure than
    lack of INTELLIGENCE or ABILITY.

    -sutnarcha-

  5. #5
    Junior Member
    Join Date
    Aug 2008
    Answers
    1

    Re: Retrieve data from emp table

    Hi
    try this query

    select to_char(hiredate,'yyyy') from emp group by to_char(hiredate,'yyyy') having count(to_char(hiredate,'yyyy'))= (select max(count(*)) from emp group by to_char(hiredate,'yy'));

    Regards
    Sasikumar


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