Are you an idiot? ----- Original Message ----- To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Friday, October 04, 2002 11:13 AM
> HELP! > > -----Original Message----- > Sent: 04 October 2002 14:53 > To: Multiple recipients of list ORACLE-L > > > sql server 7??? > > and you are trying to find an answer on Oracle board? > > Igor Neyman, OCP DBA > [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> > > > > > ----- Original Message ----- > To: Multiple recipients of list ORACLE-L <mailto:[EMAIL PROTECTED]> > Sent: Friday, October 04, 2002 2:23 AM > > Hi naveen, > > cannot perform an aggregate function on an expression containing an > aggregate or subquery > > is the error i am getting while i am executing the query. i am executing > this query in sql server 7 > > santosh > > -----Original Message----- > [mailto:[EMAIL PROTECTED]]On Behalf Of Naveen Nahata > Sent: Thursday, October 03, 2002 9:03 PM > To: Multiple recipients of list ORACLE-L > > > Santosh, > > your query is working. See below > > SQL> CREATE TABLE CLIENT ( > 2 CLIENTID NUMBER PRIMARY KEY, > 3 NAME VARCHAR2(10) > 4 ); > > Table created. > > SQL> CREATE TABLE PROJECT( > 2 PROJECTID NUMBER PRIMARY KEY, > 3 NAME VARCHAR2(10), > 4 CLIENTID REFERENCES CLIENT(CLIENTID) > 5 ); > > Table created. > > SQL> insert into client values(1, 'Naveen'); > > 1 row created. > > SQL> insert into client values(2, 'Santosh'); > > 1 row created. > > SQL> insert into project values(1, 'Oracle', 1); > > 1 row created. > > SQL> insert into project values(2, 'Java', 1); > > 1 row created. > > SQL> insert into project values(3, 'SQL', 2); > > 1 row created. > > SQL> commit; > > Commit complete. > > SQL> ed > Wrote file afiedt.buf > > 1 SELECT name > 2 FROM (SELECT c.name, COUNT(p.clientid) p_count > 3 FROM client c, project p > 4 WHERE c.clientid = p.clientid > 5 GROUP BY c.name) a, > 6 (SELECT MAX(COUNT(clientid)) p_max > 7 FROM project > 8 GROUP BY clientid) b > 9* WHERE a.p_count = b.p_max > SQL> / > > NAME > ---------- > Naveen > > SQL> I can run your query, then what's the problem? > SQL> > > Regards > Naveen > > -----Original Message----- > Sent: Thursday, October 03, 2002 2:53 PM > To: Multiple recipients of list ORACLE-L > > > cannot perform an aggregate function on an expression containing an > > aggregate or subquery > > is the error i am getting while i am executing the query. > > SELECT name > > FROM (SELECT c.name, COUNT(p.clientid) p_count > > FROM client c, project p > > WHERE c.clientid = p.clientid > > GROUP BY c.name) a, > > (SELECT MAX(COUNT(clientid)) p_max > > FROM project > > GROUP BY clientid) b > > WHERE a.p_count = b.p_max > > clientid and name are the columns in client table > > and projectid and clientid are the columns in project table. > > santosh > > -----Original Message----- > > Ignaszak > > Sent: Monday, September 30, 2002 6:09 PM > > To: Multiple recipients of list ORACLE-L > > > > 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). > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.com > > -- > > Author: Santosh Varma > > 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). > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Thomas, Kevin > 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ruth Gramolini 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).
