Re: SELECT DISTINCT problems

SCAHILL KEVIN <9726209(at)student(dot)ul(dot)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