Hi, You need:
select job,avg(sal) from emp group by 1 order by 2 limit 1; Cheers, Andrew -----Original Message----- From: Edouard Lauer [mailto:[EMAIL PROTECTED] Sent: Saturday 31 January 2004 19:23 To: [EMAIL PROTECTED] Subject: Query problem Hello, I would like to query the littlest average salary. I have a table with employees and their salary like that: +-----------+------+ | job | sal | +-----------+------+ | CLERK | 800 | | SALESMAN | 1600 | | SALESMAN | 1250 | | MANAGER | 2975 | | SALESMAN | 1250 | | MANAGER | 2850 | | MANAGER | 2450 | | ANALYST | 3000 | | PRESIDENT | 5000 | | SALESMAN | 1500 | | CLERK | 1100 | | CLERK | 950 | | ANALYST | 3000 | | CLERK | 1300 | +-----------+------+ Now this query returns the average salary per job: select job,avg(sal) from emp group by job --> +-----------+-----------+ | job | avg(sal) | +-----------+-----------+ | ANALYST | 3000.0000 | | CLERK | 1037.5000 | | MANAGER | 2758.3333 | | PRESIDENT | 5000.0000 | | SALESMAN | 1400.0000 | +-----------+-----------+ The final result should be: +-----------+-----------+ | job | avg(sal) | +-----------+-----------+ | CLERK | 1037.5000 | +-----------+-----------+ In ORACLE I can do it like this: select job,avg(sal) from emp group by job having avg(sal)=(select min(avg(sal)) from emp group by job); but this doesn't work in MYSQL. Does somebody know how it can be done in MySQL??? Regards, Edi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
