pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSELECT 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