Retrieve Rows based on distinct value 2

[xikenx]

Hi All, I hope someone can help me out with this query.

Lets say I have a table with 4 columns. Person ID, Friend's Last Name, Friend's Number.

Each person id has at least 2 friends, some have different last names and some have same last names. For example, person 1 can have John Doe, Jane Doe, Billy Bob as friends and person 2 can have Sam Wise, Peter Piper, Jane Doe as friends... so the table would look like this:

person id friend's last name friend's number
1 Doe 5555551212
1 Doe 5555555555
1 Bob 5551565555
2 Wise 5155556532
2 Piper 1532565152
2 Doe 5238545623

I am trying to figure out how to select ONLY ONE friend for each Person ID. I don't care who I select, I just want to pick one friend. Using the above example, my result could be:
person id friend's last name friend's number
1 Doe 5555551212
2 Wise 5155556532

I can figure out one way of doing this, using cursors. But can I do this without cursors?

 

[vongrunt]

> I don't care who I select, I just want to pick one friend.

Then try simple MIN() or MAX() query...


------
[small]<this is sig>
select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')
</this is sig>[/small]

 

[macleod1021]

>>> <this is sig>


BAHAHAHAHAHA

I was just telling a friend at work about the person who added your sig select to the code you wrote for them

 

[xikenx]

If I use min(), max() query as such:

select [person id], min([friend's last name] 'last name', min([friend's number]) 'number'
from tblTable
group by [person id]

this would return me mininum values for friend's last name and friend's number for each person id. For a person id, friend's last name might not be returned with his/her correct number.

 

[vongrunt]

I was thinking about:

select T.*
from tblTable T
inner join
(	select [person id], min([friend's number]) as maxfNumber
	from tblTable
	group by [person id]
) T2
on T.[person id] = T2.[person id] and T.[friend's number] = T2.maxfNumber

This assumes friend's number is unique per PersonID.

Btw. there are situations when picking "any record" with MIN/MAX is not good because some rows are automatically "preferred". If you need "any random record", that's another story.

------
[small]<this is sig>
select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')
</this is sig>[/small]

 

[SQLDenis]

select t1.*
from (select max([friend's number]) as Maxnum,personid
from tblTable
group by personid) t2 join tblTable t1 on t2.Maxnum = t1.[friend's number] 
and t1.personid=t2.personid

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[xikenx]

Thank you for your quick and helpful replies.
The example I used was a condensed and simplified version of multiple tables I needed to work with , and friend's number wasn't unique to the person... but there was a unique value other than the phone number that did work as well. Thank you again Denis and Vongrunt for your help.