Distinct Records

[qlan]

Below is an example of what I have for my Employees Table:

Name SSN Phone# ZipCode
Smith 123-45-8856 (817) 377-8825 76017
Mary 123-45-8857 (972) 422-1526 76015
Peter 123-45-8858 (972) 422-1528 76018
Paul 123-45-8859 (972) 422-1521 76017
Steve 123-45-8855 (972) 422-1522 76015
John 123-45-8860 (972) 422-1523 76018

I want to select everything from this table with distinct ZipCode. In this case, my query should return something like

Name SSN Phone# ZipCode
Smith 123-45-8856 (817) 377-8825 76017
Mary 123-45-8857 (972) 422-1526 76015
Peter 123-45-8858 (972) 422-1528 76018. How can I make this happen. It doesn't matter which record under ZipCode 76017 it should return. Thanks so much!

 

[lespaul]

Maybe:

SELECT [Name], SSN, [Phone#], FIRST(ZipCode} FROM TableName

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[Golom]

Leslie ... Other way around I think

Select First([Name]), First([SSN]), First([Phone#]), ZipCode

From myTable 

Group By ZipCode

 

[lespaul]

ok, I don't use FIRST much, but I knew it would do what the OP was asking, thanks for clearing that up!

les

 

[qlan]

Hi,
I tried with the below sql
SELECT First(Name) AS Name, First(Phone#) AS Phone#, ZipCode
From tblTest
GROUP BY ZipCode

and it gives me this error message "FIRST is not a recognized function name". Do you have any idea. Thanks

 

[lespaul]

what version of Access are you using?

Leslie

 

[qlan]

I am using SQL Server 2000.

 

[randy700]

I am using SQL Server 2000

Question: Why not post this in the SQL forum?


Randy

 

[Golom]

As randy700 says ...

It isn't working because FIRST is an Access construct and is not available in SQL Server.