Home » SQL & PL/SQL » SQL & PL/SQL » display mgrno, and salary exlude groups where sal<1000 sort in desc.
display mgrno, and salary exlude groups where sal<1000 sort in desc. [message #235987] Tue, 08 May 2007 10:59 Go to next message
Maple50175
Messages: 16
Registered: April 2007
Location: MA
Junior Member

I really appricate the help.

From what I already know:
ORDER BY MIN(SAL) DESC



I need to display the manager number and the salary of the lowest paid employee for that manager. Exclude anyone whose manager is not know-n. Exclude any groups where the minimum salary is less than $1000. Sort the output in descending order of salary.

MGR MIN (SAL)
--------- --------------
7566 3000
7839 2450
7782 1300
7788 1100
Re: display mgrno, and salary exlude groups where sal<1000 sort in desc. [message #235988 is a reply to message #235987] Tue, 08 May 2007 11:12 Go to previous messageGo to next message
Maple50175
Messages: 16
Registered: April 2007
Location: MA
Junior Member

so far I have :


SQL> SELECT a.mgr,min(a.sal)
2 from emp a,emp b
3 where a.mgr=b.empno
4 GROUP BY A.MGR
5 ORDER BY 2 DESC


but i have an error


ERROR at line 2:
ORA-00923: FROM keyword not found where expected
Re: display mgrno, and salary exlude groups where sal<1000 sort in desc. [message #235989 is a reply to message #235987] Tue, 08 May 2007 11:26 Go to previous messageGo to next message
Maple50175
Messages: 16
Registered: April 2007
Location: MA
Junior Member

error fixed


How do I make it NOT list people who make 1000$ or less..

SQL> Select a.mgr,min(a.sal)
2 from emp a,emp b
3 where a.mgr=b.empno
4 GROUP BY A.MGR
5 ORDER BY 2 DESC;

MGR MIN(A.SAL)
---------- ----------
7566 3000
7839 2450
7782 1300
7788 1100
7698 950
7902 800
Re: display mgrno, and salary exlude groups where sal<1000 sort in desc. [message #235995 is a reply to message #235989] Tue, 08 May 2007 11:42 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Wonderful! You fixed an error that did not exist! Smile

Now search for HAVING clause in documentation to "exclude any groups where the minimum salary is less than $1000".

Regards
Michel
Previous Topic: A single SQL command run for 8 minutes ?? Something wrong ??
Next Topic: .xls to oracle table
Goto Forum:
  


Current Time: Sat Dec 03 03:47:04 CST 2016

Total time taken to generate the page: 0.12984 seconds