distinct only on one column

[tyutghf]

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?

 

[TysonLPrice]

What determines the most recent vote?

 

[tyutghf]

there is an auto inc id value also that I forgot to include in the example )

 

[bborissov]

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.

 

[markros]

SQL Server 2005 and up

;with cte as (select *, row_number() over (partition by Colleage order by ID DESC) as RowNum from myTable)

select colleague,  vote1,   vote2,    vote3 from cte where RowNum = 1

Other solutions for this problem see at

http://blogs.lessthandot.com/index....rver/including-an-aggregated-column-s-related