By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,426 Members | 1,729 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,426 IT Pros & Developers. It's quick & easy.

SELECT DISTINCT problem

P: n/a

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
Nov 20 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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.
Nov 20 '05 #2

P: n/a
"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

Nov 20 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.