pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Richard Huxton <dev@archonet.com>
To: SCAHILL KEVIN <9726209@student.ul.ie>
To: 'pgsql-sql@postgreSQL.org' <pgsql-sql@postgresql.org>
Subject: Re: SELECT DISTINCT problems
Date: Wed, 28 Feb 2001 15:59:29 -0000
Message-ID: <004901c0a19f$703d2020$1001a8c0@archonet.com> (raw)
References: <992C0C12C388D411B264009027AA341804258E7F@gabriel.ul.ie>

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




view thread (3+ messages)

Message-ID: <004901c0a19f$703d2020$1001a8c0@archonet.com>
Permalink:  ../004901c0a19f$703d2020$1001a8c0@archonet.com/
Also on:    postgresql.org/message-id/004901c0a19f$703d2020$1001a8c0@archonet.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: dev@archonet.com, 9726209@student.ul.ie
  Subject: Re: SELECT DISTINCT problems
  In-Reply-To: <004901c0a19f$703d2020$1001a8c0@archonet.com>
  References: <992C0C12C388D411B264009027AA341804258E7F@gabriel.ul.ie> <004901c0a19f$703d2020$1001a8c0@archonet.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox