Select Statement problem - SQL Server Forums

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

SQL Server Development (2000)

Select Statement problem

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-03-08 : 09:51:58

cubix writes "I have got a table which has got fields familyid and firstname.

for e.g

Familyid FirstName
1 peter
2 johns
2 paul
3 mathews

I want a query to select the first name having familyid distinct.
if there are more than one records having the same family id only one of them should be
retrieved

i.e the result should be

Familyid FirstName
1 peter
2 johns
3 mathews"

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2002-03-08 : 09:55:50

for the rows which duplicate familyID, you need to determine which row you want to keep. the nature of SQL is such that you must explicitly describe how to make that determination; you can't ask for an arbitrary selection.

in your example, if you wanted to pick John over Paul because John is before Paul alphabetically, you could write:

select FamilyID, min(FirstName) as FirstName
from [table name]
group by FamilyID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources