Home » SQL & PL/SQL » SQL & PL/SQL » Join Query
Join Query [message #309087] Wed, 26 March 2008 06:57 Go to next message
deep_gh1
Messages: 13
Registered: October 2006
Location: India
Junior Member

HOW DO I GET MAXIMUM NUMBER OF EMPLOYEES WORKING IN A PARTICULAR DEPARTMENT...?

THAT IS,

SELECT * FROM EMP;
SELECT * DEPT;

AND THE OUTPUT SHOULD BE.....

NO_OF_EMPS | DNAME
--------------- | ----------
6 | SALES

Thanks,

Sudip
Re: Join Query [message #309091 is a reply to message #309087] Wed, 26 March 2008 07:18 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
set colsep '|'
select 6 NO_OF_EMPS, 'SALES' DNAME from dual;

Regards

Raj

[Updated on: Wed, 26 March 2008 07:19]

Report message to a moderator

Re: Join Query [message #309100 is a reply to message #309087] Wed, 26 March 2008 07:29 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Please do not post in all-caps, as this means shouting and is considered as rude.
Re: Join Query [message #309101 is a reply to message #309091] Wed, 26 March 2008 07:29 Go to previous messageGo to next message
deep_gh1
Messages: 13
Registered: October 2006
Location: India
Junior Member

No no...I wanted to say that by joining emp amd dept table I want to fetch the highest number of employees working in a particular department...so the o/p should show like the max number of emp and corressponding department name...

Thanks,

Sudip
Re: Join Query [message #309110 is a reply to message #309087] Wed, 26 March 2008 07:56 Go to previous messageGo to next message
suresh_oradba
Messages: 5
Registered: September 2007
Junior Member
We do not know your EMP table, do not know DEPT table, nor the relationships. without any table creation scripts, only way you can get what you want is what Rajaram suggested.
Re: Join Query [message #309115 is a reply to message #309087] Wed, 26 March 2008 08:11 Go to previous messageGo to next message
ZeljkoV
Messages: 3
Registered: March 2008
Junior Member
This should help, I hope

SELECT department_id, count(*)
FROM employees
GROUP BY department_id
HAVING COUNT(department_id) =
(SELECT max(count(department_id))
FROM employees
GROUP BY department_id);
Re: Join Query [message #309119 is a reply to message #309087] Wed, 26 March 2008 08:28 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Always post your Oracle version (4 decimals).

You can also use ROW_NUMBER, RANK and DENSE_RANK functions.

Regards
Michel
Previous Topic: Time from Date field
Next Topic: INFO REQ on string manipulation
Goto Forum:
  


Current Time: Thu Dec 08 18:34:12 CST 2016

Total time taken to generate the page: 0.13925 seconds