469,607 Members | 2,060 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,607 developers. It's quick & easy.

SELECT DISTINCT problem


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
2 1329
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
"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.

Similar topics

5 posts views Thread by Colman | last post: by
5 posts views Thread by Martin Feuersteiner | last post: by
3 posts views Thread by Tcs | last post: by
9 posts views Thread by Kelvin | last post: by
5 posts views Thread by Daniel Wetzler | last post: by
reply views Thread by guiromero | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.