Thread: SELECT DISTINCT problems

Hi there,
I'm having a lot of trouble with one sql statement and I wonder can you
help.

I My problem is the following...there are two field name in the the table
named LecturerName and Projectcode. Each ProjectCode vulue is unique within
the table but there can be many entries in the table with the same
LecturerName.

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. 


I am trying to put the results of this query into a recordset and I am using
an accessdatabase 
Thanks in advance,
Kevin.

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

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

Tom,
A while back you posted a rather intimidating e-mail regarding
updatable views.  Thanks to some reading (Fabian Pascal) I understand
what you were talking about.  My question is, are updatable views
implemented in 7.1, or is this still vaporware?
And, for that matter, when is 7.1 expected to be out of beta?
                -Josh Berkus


P.S. Let me suggest again that you let us application developer-types
field the newbie questions so that you can focus on the hard issues and
developing the database engine.


-- 
______AGLIO DATABASE SOLUTIONS___________________________                                       Josh Berkus  Complete
informationtechnology      josh@agliodbs.com   and data management solutions       (415) 565-7293  for law firms, small
businesses      fax  621-2533   and non-profit organizations.       San Francisco