SELECT DISTINCT problem

[ray well]

i have to extract info from a legacy access database, which i can't alter,
or run APPEND or UPDATE quries against. i can only use SELECT statments to
extract what i need.

the database has multiple entries for the same first and last name, i need
to generate a dataset that has no duplicate first and last names.

the sql statment itself is quite easy

SELECT DISTINCT tblNameAndAddress.LastName, tblNameAndAddress.FirstName
FROM tblNameAndAddress;

my problem is, i also need to extract an additonal field, UniqueID, which is
NOT the same in the multiple entries, so i can't add UniqueID to the SELECT
DISTINCT statement, because then it will bring up all the multiple entries.

any idea how i can solve this?

ray

[Chris Dunaway]

On Fri, 12 Dec 2003 12:48:47 -0500, ray well wrote:

my problem is, i also need to extract an additonal field, UniqueID, which is
NOT the same in the multiple entries, so i can't add UniqueID to the SELECT
DISTINCT statement, because then it will bring up all the multiple entries.

If the uniqueid is different for each of these records, how will you know
you have the correct one? By what criteria should the SELECT statement
decide which record to return in the DISTINCT clause?

e.g. If you have records like this:

LastName, FirstName, UniqueId

Jones John 0
Jones Jane 1
Jones John 2
Jones Jane 3

When you call your select statement, which records do you want to return?
How do you decide?

Can't you add the UniqueID to the Select like this:

Select Distinct LastName, FirstName, UniqueId
From tblNameAndAddress
Where UniqueId = <someUniqueIdValueHere

Hope this helps a little

--
Chris

To send me an E-mail, remove the underscores and lunchmeat from my E-Mail
address.

[Armin Zingler]

"ray well" <no****@nospam.com> schrieb

i have to extract info from a legacy access database, which i can't
alter, or run APPEND or UPDATE quries against. i can only use SELECT
statments to extract what i need.

[...]

For ADO.NET related questions please turn to
microsoft.public.dotnet.framework.adonet
--
Armin

http://www.plig.net/nnq/nquote.html
http://www.netmeister.org/news/learn2quote.html