MS SQL SERVER MAX function on one column, return all columns : I'm using MS SQL Server and I'm trying to get the MAX of Res when Res is a varchar, and I want to group by pID, but also keep the aID in the final result. 


aID | Res | pID

1  | Yes      | 94 <br>
2  | Yes      | 32 <br>
3  | No       | 32 <br>
4  | Yes      | 94

    SELECT aID, max(Res), pID
    FROM Table1
    GROUP BY pID

My final result should be:

aID | Res | pID

1  | Yes      | 94 <br>
2  | Yes      | 32 <br>

or <br>

aID | Res | pID

4  | Yes      | 94 <br>
2  | Yes      | 32 <br>

The whole issue is that I can't include aID in the final result. I've tried a sub query where Res is = to max(Res), but with over 50,000 records, it's taking over 20 minutes to run this thing. There's got to be a better way. Is there?