Correct. "Select Distinct" selects distinct records for ALL of the columns
that you supplied. i.e.
"SELECT DISTINCT name From table" selects one record for each distinct name.
"SELECT DISTINCT name, otherfield From table" selects one record for each
distinct combination of name and other field.
What you can do is select all distinct names into a temporary table and then
do a second select combining these records and the additional info from the
table based on a the primary key.
-----Original Message-----
From: DeVoil, Nick [mailto:[EMAIL PROTECTED]]
Sent: 14 August 2000 11:37
To: '[EMAIL PROTECTED]'
Subject: RE: Select Distinct not so distinct
> I'm trying to select all of the values from a table where type=foo and I
> would like to eliminate duplicates based on name.
>
> EG.
> SQL="select distinct(name), other, field, names from table where type =
foo"
> -this returns every field where type is a match.
> SQL="select distinct(name) from table where type = foo"
> -this works but does not retrieve additional field info.
>
> Am I to understand that select distinct cannot be utilized in this
fashion?
> It works if I select the name field only, but if I want more than the
name
> field it returns all matches. I cannot ask that the database be
restructured.
But if there are n rows sharing the same value in the 'name' column, which
of those rows do you want the query to return??? Or do you want the values
in the other columns aggregated in some way?
Nick
**********************************************************************
Information in this email is confidential and may be privileged.
It is intended for the addressee only. If you have received it in error,
please notify the sender immediately and delete it from your system.
You should not otherwise copy it, retransmit it or use or disclose its
contents to anyone.
Thank you for your co-operation.
**********************************************************************
----------------------------------------------------------------------------
--
Archives: http://www.mail-archive.com/[email protected]/
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or
send a message to [EMAIL PROTECTED] with 'unsubscribe' in
the body.
------------------------------------------------------------------------------
Archives: http://www.mail-archive.com/[email protected]/
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
