Selecting only one record for each distinct value in a column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Selecting only one record for each distinct value in a column

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-03-10 : 07:22:29

Kalyan writes "I need to query a table and return only one record for each distinct value in a column. Consider the following people table example:

FirstName LastName Address City State
--------- -------- ------- ---- -----
John Bradley 1 Test NoWhere MD
Jill Bradley 10 Test NoWhere MD
Tom Hayworth 2 Test NoWhere MD

*********************************
The query when tested for records with distinct last names should return one of the following set of records:
John Bradley 1 Test NoWhere MD
Tom Hayworth 2 Test NoWhere MD

or

Jill Bradley 10 Test NoWhere MD
Tom Hayworth 2 Test NoWhere MD
*********************************

That is, it should return a representative record for every value in the specified column.

Can someone help me how to do this?
Thanks,"

joldham
Wiseass Yak Posting Master

300 Posts

Posted - 2003-03-10 : 07:58:43

Well that all depends on which dataset you want to return.

You sould use something like the following:

Select b.FirstName, a.LastName, b.Address, b.City, b.State
From (
Select LastName, Max(Address) as Address
From myTable
GROUP BY FirstName, LastName) a
INNER JOIN myTable b
and a.LastName = b.LastName
and a.Address = b.Address

The Max(address) part of the query in this case would return the '10 Test' and 'John' record for the Bradley last name. If you changed this to Min(Address) then it would return the '1 Test' and 'Jill' record for the Bradley last name.

Hope this helps.

Jeremy

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources