try it: select name from (select c.name, count(p.id) p_count from clients c, projects p where c.id = p.cl_id group by c.name) a, (select max(count(id)) p_max from projects group by cl_id) b where a.p_count = b.p_max
Regards, Leszek At 03:23 2002-09-30 -0800, you wrote: >Hello all, > > I have a query -> >i have 2 tables -> client and project > >fields in project table -> clientid/projectid >fields in client table -> clientid/name > >i want to get the maximum orders one client has got. i mean a project >having the greatest clients >how to write it in single query ?? > > >like >project 1 client 1 >project 2 client 1 >project 3 client 2 > >in the above case, the query should return client ( 1 ). > >Thanks and regards, >Santosh -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Leszek Ignaszak INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).