SELECT DISTINCT problem

  • Thread starter Thread starter ray well
  • Start date Start date
R

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
 
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.
Click to expand...

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
 
ray well said:
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.

[...]
Click to expand...

For ADO.NET related questions please turn to
microsoft.public.dotnet.framework.adonet
 
Back
Top