Home » SQL & PL/SQL » SQL & PL/SQL » In which year maximum number of employees has been recruited(hire date)using employees table(oracle)
In which year maximum number of employees has been recruited(hire date)using employees table(oracle) [message #435192] Tue, 15 December 2009 01:45 Go to next message
pretty_baby1984@yahoo.com
Messages: 7
Registered: November 2009
Junior Member
Hello All,
I am struck with finding the result of the following query,

Q) In which year maximum number of employees has been recruited(hire_date)using either emp or employees table.(Oracle)

My Query:
select max(count(employee_id))
from employees
group by substr(to_date(hire_date,'dd-mon-yy'),8 )

I could get the max count using the above query but I am not able to pull the year out of it.Please help me in finding the solution.

--
Thanks & Regards
Sujatha
Re: In which year maximum number of employees has been recruited(hire date)using employees table(ora [message #435193 is a reply to message #435192] Tue, 15 December 2009 01:57 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
SQL>  select to_char(hiredate,'yyyy'),count(*)
  2   from emp
  3   having count(*)>=(select max(count(*)) from emp group by  to_char(hiredate,'YYYY'))
  4   group by to_char(hiredate,'yyyy')
  5  /

TO_C   COUNT(*)
---- ----------
1981         10
Re: In which year maximum number of employees has been recruited(hire date)using employees table(ora [message #435194 is a reply to message #435192] Tue, 15 December 2009 02:06 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select extract(year from sysdate) the_year from dual;
  THE_YEAR
----------
      2009

1 row selected.

Regards
Michel
Re: In which year maximum number of employees has been recruited(hire date)using employees table(ora [message #435195 is a reply to message #435192] Tue, 15 December 2009 02:07 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
to_date(hire_date,'dd-mon-yy')

As hire_date is a date to_date of a date is a nonsense more it is an error:
SQL> select to_date(hiredate,'dd-mon-yy') from emp;
select to_date(hiredate,'dd-mon-yy') from emp
               *
ERROR at line 1:
ORA-01843: not a valid month


Regards
Michel

[Updated on: Tue, 15 December 2009 02:08]

Report message to a moderator

Re: In which year maximum number of employees has been recruited(hire date)using employees table(ora [message #435199 is a reply to message #435195] Tue, 15 December 2009 02:47 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
SQL> desc emp;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPNO                                              NUMBER(4)
 ENAME                                              VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 MGR                                                NUMBER(4)
 HIREDATE                                           DATE
 SAL                                                NUMBER(7,2)
 COMM                                               NUMBER(7,2)
 DEPTNO                                             NUMBER(2)

SQL> set line 1000
SQL> select to_date(hiredate,'dd-mon-yy') from emp;

TO_DATE(H
---------
17-DEC-80
20-FEB-81
22-FEB-81
02-APR-81
28-SEP-81
01-MAY-81
09-JUN-81
19-APR-87
17-NOV-81
08-SEP-81
23-MAY-87
03-DEC-81
03-DEC-81
23-JAN-82

14 rows selected.


So it depends.And more over this kind of topic already discussed that if one get the correct answer that it is not like every one should get the same result.So...Here just included that for me it wont through any error.


sriram Smile

[Updated on: Tue, 15 December 2009 02:49]

Report message to a moderator

Re: In which year maximum number of employees has been recruited(hire date)using employees table(ora [message #435201 is a reply to message #435199] Tue, 15 December 2009 02:56 Go to previous messageGo to next message
ThomasG
Messages: 3184
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Ah. So you are one of the "If the code works 50% of the time by chance, it's OK" people that drives everyone insane trying to deal with their code.

Using to_date on a date is a bug. Of the same seriousness as using a number(1) field to store a month. Hey, it works 9 times out of 12, so it's OK.
Re: In which year maximum number of employees has been recruited(hire date)using employees table(ora [message #435202 is a reply to message #435201] Tue, 15 December 2009 03:00 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
@Thomas

What ever Michel said is correct. I agree.
Quote:
As hire_date is a date to_date of a date is a nonsense
.

But Mine is for the other.I am not pointing any mistake in Michel`s post.He is the senior here.
This what i had in my previous post...
http://www.orafaq.com/forum/m/430948/136607/#msg_430948 Both are similar again..

Sriram Smile

[Updated on: Tue, 15 December 2009 03:04]

Report message to a moderator

Re: In which year maximum number of employees has been recruited(hire date)using employees table(ora [message #435204 is a reply to message #435202] Tue, 15 December 2009 03:09 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ramoradba wrote on Tue, 15 December 2009 10:00
@Thomas

What ever Michel said is correct. I agree.
Quote:
As hire_date is a date to_date of a date is a nonsense
.

But Mine is for the other.I am not pointing any mistake in Michel`s post.He is the senior here.
This what i had in my previous post...
http://www.orafaq.com/forum/m/430948/136607/#msg_430948 Both are similar again..

Sriram Smile

And my is the same as in the previous topic you pointed:
Michel Cadot wrote on Fri, 13 November 2009 13:37
Quote:
Michel sir,If the query not executed,How anand,cookie,me posting the sql sessions here?

I posted the result from sqlplus cmd promt

Or shall i post the screen shot to prove?

You do not have to prove me it works for you, I trust you.
But as it does not work for me it means that the query is not correct, otherwise it'd work for anyone.

Regards
Michel

Why do you stay to reply the same incorrect stuff that will lead beginner readers that they can do this.
TO_DATE on a date is just STUPID and more will lead to your application to fail.
So IT IS A BUG.
Full stop.

Regards
Michel


Re: In which year maximum number of employees has been recruited(hire date)using employees table(ora [message #435207 is a reply to message #435192] Tue, 15 December 2009 03:13 Go to previous messageGo to next message
ajay.oracle
Messages: 3
Registered: October 2008
Location: India
Junior Member
Hi,

select hiredate, count(1)
from emp
where hiredate = (select max(hiredate) from emp)
group by hiredate;
/

Re: In which year maximum number of employees has been recruited(hire date)using employees table(ora [message #435209 is a reply to message #435192] Tue, 15 December 2009 03:16 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Here's a way of doing it without nested queries:
create table test_113 (col_1 date, col_2 number);

insert into test_113 values  (sysdate,1);
insert into test_113 values  (sysdate+300,1);
insert into test_113 values  (sysdate+600,1);
insert into test_113 values  (sysdate+900,1);
insert into test_113 values  (sysdate+1200,1);
insert into test_113 values  (sysdate+300,1);
insert into test_113 values  (sysdate+600,1);
insert into test_113 values  (sysdate+900,1);
insert into test_113 values  (sysdate+600,1);

commit;


select max(trunc(col_1,'Y')) keep (dense_rank last order by sum(col_2))
from   test_113
group by trunc(col_1,'Y');


You'll probably want to read some documentation.
Re: In which year maximum number of employees has been recruited(hire date)using employees table(ora [message #435211 is a reply to message #435202] Tue, 15 December 2009 03:25 Go to previous messageGo to next message
Littlefoot
Messages: 20826
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
ramoradba wrote on Tue, 15 December 2009 10:00
He (Michel) is the senior here.

So what? Being a senior member doesn't mean that you can't question his/her answers. Seniors (OraFAQ Forum senior members, precisely) can also be wrong (just look at me, a great example!).

Besides, Michel was also just a junior member (that was his status only for a few hours, though) and a member (a day or so) ./fa/451/0/.
Re: In which year maximum number of employees has been recruited(hire date)using employees table(ora [message #435225 is a reply to message #435192] Tue, 15 December 2009 05:04 Go to previous messageGo to next message
pretty_baby1984@yahoo.com
Messages: 7
Registered: November 2009
Junior Member
Thank you all for the responses....They are working...

I would like to say something...using the word "non sense" is inappropriate..Its not necessary that every one should know everything and more over we do post the questions here because we don't know the answer..

Anyhow,thanks again for replies...

--
Sujatha

Re: In which year maximum number of employees has been recruited(hire date)using employees table(ora [message #435254 is a reply to message #435225] Tue, 15 December 2009 06:30 Go to previous message
pablolee
Messages: 2813
Registered: May 2007
Location: Scotland
Senior Member
pretty_baby1984@yahoo.com wrote on Tue, 15 December 2009 11:04
I would like to say something...using the word "non sense" is inappropriate..Its not necessary that every one should know everything and more over we do post the questions here because we don't know the answer..


On this you are wrong.
The context of the use of the word is important:
Quote:
As hire_date is a date to_date of a date is a nonsense

Using a to_date on a date does not make sense. It is nonsense, and the use term is entirely appropriate.
When you also consider that the 'target' of the quote has been told before that putting a to_date around a date of nonsense, that simply cements the appropriateness of the use of the term.

regards
Previous Topic: BULK DELETE
Next Topic: query help
Goto Forum:
  


Current Time: Tue Sep 27 07:41:42 CDT 2016

Total time taken to generate the page: 0.12651 seconds