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
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.
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
Re: Retrieve data from emp table
This WILL work :D
[B]SELECT TOP 1 Count(*) AS C1, Year(hiredate) AS C2 FROM emp GROUP BY Year(hiredate) ORDER BY C1 DESC;[/B]
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