SCAHILL KEVIN <[EMAIL PROTECTED]> 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
