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 derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

distinct only on one column

Status
Not open for further replies.

tyutghf

Technical User
Joined
Apr 12, 2008
Messages
258
Location
GB
I have a table where users could vote on a subject, but the script allowed people to vote more than once when only one vote per person is allowed. I can determine who is who on the 'colleague' column

i.e.

colleague vote1 vote2 vote3
1 a b c
2 c b a
1 b c a

As you can see, colleague a voted twice, I only want to take their most recent vote.

If I use select distinct colleague, vote1, vote2, vote3 from table it is picking all as they are actuially different.

What I need is to only select distinct on the colleague column but my query needs to output all rows.

Does that make sense? Can I structure a SQL query that will do what I want?
 
there is an auto inc id value also that I forgot to include in the example :o)

 
Code:
SELECT YourTable.*
FROM YourTable
INNER JOIN (SELECT MAX(IdentityFieldName) AS IdentityFieldName, 
                   colleague  
            FROM YourTable
            GROUP BY colleague) Tbl1
      ON YourTable.IdentityFieldName = Tbl1.IdentityFieldName

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top