Home » SQL & PL/SQL » SQL & PL/SQL » Help me
Help me [message #301558] Thu, 21 February 2008 00:53 Go to next message
thani_oracle
Messages: 44
Registered: August 2007
Location: Bangalore
Member

Hi,
Can any one help me, How to find max of department. employee details. How to write using sql
Re: Help me [message #301563 is a reply to message #301558] Thu, 21 February 2008 01:00 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
what do you want to know?
1 - max emp in each dept or
2 - max deptno.

regards,
Re: Help me [message #301566 is a reply to message #301558] Thu, 21 February 2008 01:03 Go to previous messageGo to next message
thani_oracle
Messages: 44
Registered: August 2007
Location: Bangalore
Member

Hi,

Maximum of Department .. Employee Details

like maximum of department empno,ename,sal,dname i want

Thanks,
Thani.
Re: Help me [message #301590 is a reply to message #301566] Thu, 21 February 2008 01:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64145
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post what you tried, where you are stuck.
You have the EMP table? Post the result you want with it.
If you don't have it, then create your own test case and post it along with the result you want with your data.

Regards
Michel
Re: Help me [message #301952 is a reply to message #301566] Fri, 22 February 2008 08:00 Go to previous messageGo to next message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
Search here or Google for the MAX function.
Re: Help me [message #303168 is a reply to message #301558] Thu, 28 February 2008 03:45 Go to previous message
thani_oracle
Messages: 44
Registered: August 2007
Location: Bangalore
Member

SQL> select empno,ename,sal,e.deptno,dname
  2  from emp e,dept d
  3  where e.deptno = d.deptno
  4  and e.deptno = (select deptno
  5  from emp
  6  group by deptno
  7  having count(deptno) = (
  8                     select max(temp.tot) as from dual,
  9                             (select count(*) as Tot,deptno
 10                                     from emp
 11                                     group by deptno
 12                                     order by Tot desc)  temp));

     EMPNO ENAME             SAL     DEPTNO DNAME
---------- ---------- ---------- ---------- --------------
      7499 ALLEN            1600         30 SALES
      7521 WARD             1250         30 SALES
      7654 MARTIN           1250         30 SALES
      7698 BLAKE            2850         30 SALES
      7844 TURNER           1500         30 SALES
      7900 JAMES             950         30 SALES

6 rows selected.

Sorry Masters I have posted for this Result only.

[Mod-edit: Frank added [code] and [/code] tags around your code. Please do so yourself next time]

[Updated on: Thu, 28 February 2008 04:31] by Moderator

Report message to a moderator

Previous Topic: relational operator in decode
Next Topic: pivoting numbers and bring the names instead of numbers
Goto Forum:
  


Current Time: Fri Dec 09 11:43:36 CST 2016

Total time taken to generate the page: 0.13682 seconds