Distinct query issue 2

[ItHurtsWhenIThink]

I have a very simple query. But, it just does not provide the results I want. Need to query table and get rows with distinct phone number. Do not want duplicate phone numbers.

Here is my query that looks like it should work. Currently gives my duplicate phone numbers.

SELECT Distinct(Phone), FName, LName, City, Address, Zip
FROM Voters
WHERE Phone Like '523*' AND City='WhoVille'
ORDER BY Phone;

should work heh?

 

[PHV]

What is the PrimaryKey of Voters ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ItHurtsWhenIThink]

VoterID

But there can be mutliple voters in one househol with the same phone number.

ie,

Voter ID Phone address LName
1 555-777-9999 123rd Ave Jones
2 555-777-9999 123rd ave Smith
3 333-555-2212 527 main st johnson

 

[TheAceMan1]

How are ya ItHurtsWhenIThink . . .

[blue]PHV[/blue] asked about primarykey for a reason!

Try the following:

[blue]SELECT Phone, FName, LName, City, Address, Zip 
FROM Voters 
WHERE (([Voter ID] In (SELECT Max([Voter ID]) AS MaxPK 
                       FROM Voters 
                       GROUP BY Phone 
                       HAVING (Phone Like '523*' AND City='WhoVille')) 
ORDER BY Phone;[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[TheAceMan1]

Woops!

Problem with parenthese corrected below:

[blue]SELECT Phone, FName, LName, City, Address, Zip 
FROM Voters 
WHERE ([Voter ID] In (SELECT Max([Voter ID]) AS MaxPK 
                       FROM Voters 
                       GROUP BY Phone 
                       HAVING (Phone Like '523*' AND City='WhoVille'))) 
ORDER BY Phone;[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[PHV]

SELECT V.Phone, V.FName, V.LName, V.City, V.Address, V.Zip
FROM Voters V INNER JOIN (
SELECT Min(VoterID) AS myPK FROM Voters WHERE Phone Like '523*' GROUP BY Phone
) U ON V.VoterID = U.myPK
WHERE V.City='WhoVille'
ORDER BY V.Phone

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ItHurtsWhenIThink]

I used PHV's solution.

I see how you came up with the solution. The "Group By" put all the duplicate number in one group.

Worked great.
Thanks for the solution.