Select Problem

[Always Learning]

Hi Guys,

I have a file with FirstName, LastName,Address,Postcode,Email fields
There are duplicate firstname & lastname fields.
Can some one show me the sql statement I need to get distinct records where
the firstname and lastname are not the same.
I.E if one record has "Steve","Wilson","123 the glade",BR1 3OX","" and the
other record has "steve","wilson","123 the glade",BR1 3OX","" I only get one
of the records returned in the query.

Thanks for any help you can give me.

Best Regards,

Steve Wilson.

 

[Guest]

SELECT DISTINCT firstname, lastname, etc
FROM tablename

 

[Always Learning]

Hi Suzy,
Thanks for the reply.
I have used that statement but how do I get all the fields returned not just
the First & Last Name fields.

Thanks.

Best Regards,

Steve Wilson.

 

[Guest]

list them with commas between, however if anyone of them will cause the
entire line to become distinct on it's own then you will be back to your
original problem. I.E.
data is
lastname: Smith
firstname:John
address:1234 3rd street

and

lastname: smith
firstname: john
address: 4567 3rd Street

then
Select distinct lastname, firstname ...

will return one record

lastname: Smith
firstname: John

and
Select distinct lastname, firstname, address ...

will return two records
lastname: Smith
firstname:John
address:1234 3rd street

and

lastname: smith
firstname: john
address: 1234 3rd Street box 1

to fix that you can do the following

select distinct lastname, firstname, min(address) ...

this will return one record

lastname: Smith
firstname:John
address:1234 3rd street

or
select distinct lastname, firstname, max(address) ...

this will return one record

lastname: smith
firstname: john
address: 1234 3rd Street box 1