Received: from sss.pgh.pa.us (sss.pgh.pa.us [209.114.132.154]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f1SFZ8x42773 for ; Wed, 28 Feb 2001 10:35:08 -0500 (EST) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.1/8.11.1) with ESMTP id f1SFZ1918172; Wed, 28 Feb 2001 10:35:01 -0500 (EST) To: SCAHILL KEVIN <9726209@student.ul.ie> cc: "'pgsql-sql@postgreSQL.org'" Subject: Re: SELECT DISTINCT problems In-reply-to: <992C0C12C388D411B264009027AA341804258E7F@gabriel.ul.ie> References: <992C0C12C388D411B264009027AA341804258E7F@gabriel.ul.ie> Comments: In-reply-to SCAHILL KEVIN <9726209@student.ul.ie> message dated "Tue, 27 Feb 2001 18:05:58 +0000" Date: Wed, 28 Feb 2001 10:35:00 -0500 Message-ID: <18169.983374500@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200102/423 X-Sequence-Number: 1628 SCAHILL KEVIN <9726209@student.ul.ie> writes: > 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 You seem to be trying to re-invent the notion of GROUP BY. The correct way to write this sort of query in SQL is Select LecturerName, MIN(ProjectCode) from tblSuggestions GROUP BY LecturerName This gives you one output row for each distinct value of LecturerName, and within that row the MIN() aggregates over all the original rows that have that LecturerName. See http://www.postgresql.org/devel-corner/docs/postgres/query-agg.html http://www.postgresql.org/devel-corner/docs/postgres/queries.html#QUERIES-GROUP regards, tom lane