Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

return all columns on one distinct column

Status
Not open for further replies.

dkemas

Programmer
Mar 22, 2012
70
GB
I have a table with many columns, I want to return all columns but ignore duplicates based on one column

id | fullname | this | that | other
1 | Bob | 1 | 0 | 0
2 | Mary | 1 | 1 | 0
3 | Bob | 0 | 1 | 0

In the above example I need to only pull back one Bob row even though the data in fields other that fullname are different.

So I can't do

select distinct * from table

as this would pull back both Bobs

I can do

select distinct fullname from table

but this returns just the fullname column, I want all columns kind of like

select distinct fullname, * from table

but obviously that won't work. I tried a nested query and think that may be the way but am getting confused with that too

SELECT distinct fullname FROM (select * from table) as foo

How would I do this?

Thanks
 
Thanks audiopro, you put me on the right track. My final working query is

SELECT *
FROM table t1
INNER JOIN table t2 ON t1.fullname = t2.fullname
GROUP BY t1.fullname
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top