Received: from www-uk-05.digiserve.com ([195.172.23.63]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f1SG4Qx46456 for ; Wed, 28 Feb 2001 11:04:26 -0500 (EST) (envelope-from dev@archonet.com) Received: from mainbox.archonet.com (mwynhau.demon.co.uk [193.237.186.96]) by www-uk-05.digiserve.com (8.9.1/8.9.1) with ESMTP id QAA21394; Wed, 28 Feb 2001 16:05:28 GMT Received: from client (client.archonet.com [192.168.1.16]) by mainbox.archonet.com (Postfix) with SMTP id BAE372A46F; Wed, 28 Feb 2001 15:59:33 +0000 (GMT) Message-ID: <004901c0a19f$703d2020$1001a8c0@archonet.com> From: "Richard Huxton" To: "SCAHILL KEVIN" <9726209@student.ul.ie>, "'pgsql-sql@postgreSQL.org'" References: <992C0C12C388D411B264009027AA341804258E7F@gabriel.ul.ie> Subject: Re: SELECT DISTINCT problems Date: Wed, 28 Feb 2001 15:59:29 -0000 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.50.4133.2400 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4133.2400 X-Archive-Number: 200102/424 X-Sequence-Number: 1629 From: "SCAHILL KEVIN" <9726209@student.ul.ie> > I would like to pull each distinct value of LecturerName with any one > corresponding ProjectCode value, it does not matter what the ProjectCode > value is, but all attemps at this have failed so far. > > I have tried this but it does not work: > > Set rsLecturers = Server.CreateObject("ADODB.Recordset") > sqlLect = "Select LecturerName, MIN(ProjectCode) from tblSuggestions WHERE > LecturerName IN ( SELECT DISTINCT LecturerName FROM tblSuggestions)" > rsLecturers.Open sqlLect, Conn, 3, 3 > > I get this error when I try to run this: > [Microsoft][ODBC Microsoft Access Driver] You tried to execute a query that > does not include the specified expression 'LecturerName' as part of an > aggregate function. The "min()" function means this is what access refers to as a "totals query". Try something like: SELECT LecturerName,min(ProjectCode) FROM tblSuggestions GROUP BY LecturerName Basically, anything that is not min() or max()ed should be mentioned in the GROUP BY. You might need to quote "LecturerName" (like that) etc since they are mixed-case. On the other hand the ODBC might deal with all that for you. - Richard Huxton