Get a entire row based on a particular condition on adjoining column in SQL Server

Question

My table

integer   | party   | value | and many more columns
----------+---------+-------+----------------------
 1        | abc     |  40   | -----
 1        | abc     |  90   | -----
 2        | pqr     |  12   | -----
 1        | abc     | 120   | -------
 2        | pqr     |  86   | --------

and so on

I want entire row to be retrieved which has max value

Sample output for above table

1    abc    120
2    pqr     86

For a particular distinct party I want row to be picked up which has maximum value.

Answer 1

You can use the rank window function to find the "max" row per party:

SELECT 
    id, party, value
FROM
    (SELECT 
         id, party, value, 
         RANK() OVER (PARTITION BY party ORDER BY value DESC) AS rk
     FROM   
         mytable) t
WHERE  
    rk = 1

Answer 2

use simple aggregation function MAX(), assume integer is a id column

select id,party, max(value) as max_val from your_table
group by id,party