pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
SELECT DISTINCT problems
3+ messages / 3 participants
[nested] [flat]

* SELECT DISTINCT problems
@ 2001-02-27 18:05 SCAHILL KEVIN <9726209@student.ul.ie>
  2001-02-28 15:35 ` Re: SELECT DISTINCT problems Tom Lane <tgl@sss.pgh.pa.us>
  2001-02-28 15:59 ` Re: SELECT DISTINCT problems Richard Huxton <dev@archonet.com>
  0 siblings, 2 replies; 3+ messages in thread

From: SCAHILL KEVIN @ 2001-02-27 18:05 UTC (permalink / raw)
  To: pgsql-sql

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.



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: SELECT DISTINCT problems
  2001-02-27 18:05 SELECT DISTINCT problems SCAHILL KEVIN <9726209@student.ul.ie>
@ 2001-02-28 15:35 ` Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 0 replies; 3+ messages in thread

From: Tom Lane @ 2001-02-28 15:35 UTC (permalink / raw)
  To: SCAHILL KEVIN <9726209@student.ul.ie>; +Cc: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: SELECT DISTINCT problems
  2001-02-27 18:05 SELECT DISTINCT problems SCAHILL KEVIN <9726209@student.ul.ie>
@ 2001-02-28 15:59 ` Richard Huxton <dev@archonet.com>
  1 sibling, 0 replies; 3+ messages in thread

From: Richard Huxton @ 2001-02-28 15:59 UTC (permalink / raw)
  To: SCAHILL KEVIN <9726209@student.ul.ie>; pgsql-sql

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




^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2001-02-28 15:59 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-02-27 18:05 SELECT DISTINCT problems SCAHILL KEVIN <9726209@student.ul.ie>
2001-02-28 15:35 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-02-28 15:59 ` Richard Huxton <dev@archonet.com>

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