SQL distinct function - question 1

[Ovatvvon]

I have a list of people's names. 1 field is the first name, the other is the last name. (simple enough).

Now, I need to accomplish somthing similar to using the DISTINCT function where it only select 1 of each value from the whole fields contents, however, the problem is that some people with the same first names have different last names, and vice versa....Example, the list may look somthing like this...


John Rivers
Joe Smith
Joe Smith
John Deere
John Deere
Pat Miller
Sarah Smith
Joan Rivers
Pat Pickard
Pat Pickard
Sarah Smith


So I want to pick out each person from the two column set just once. If I use distinct on either the first or last names, it'll automatically weed out other people by grouping people like Joe and Sarah Smith both into the smith name, and Joan and John Rivers into the Rivers category. (and likewise for distinct on the first name).

Does anyone know if there's a way to do this when it's seperated into two columns? -Ovatvvon :-Q

 

[JuanitaC]

Try this:

Select Distinct FirstName + ' ' + LastName as [PersonName]
From tblPeople

 

[derren]

or just

select distinct firstname, lastname from tblPeople

You can use distinct on as many fields as you like. Derren
[Mediocre talent - spread really thin]

 

[Ovatvvon]

Derren, tried that, but that would defeat what I'm trying to do because it'd pull every distinct record from each field, therefore putting all Jane's together, even if there were a Jane Smith, Jane Rogers, and Jane Miller.

I'm sorry I didn't get a chance to reply before, JuanitaC, your method worked exactly how I needed it to. Thanks! -Ovatvvon :-Q

 

[derren]

Hi

Distinct will not group the names as you said, if you use more than one field it compares all of them as a whole, which is the sql equivalent to what JuanitaC said. It would not group all of your janes together.

I know you don't believe me, but try it. I have been using distinct like this for years! Derren
[Mediocre talent - spread really thin]