Get max value from result which contains different string value in group by clause in SQL Server

Question

My table is like this,

Field1   |  Field2  |  Field3

2        |    100   |  test1
3        |    100   |  test2

I need to get max from this result. so the result should be as follows,

3   100   test2

my current query is,

SELECT MAX(Field1),Field2,Field3 FROM Table1 GRUOP BY Field2,Field3

this returns these two rows. but i need only the row which contain the MAX(Field1) value.

Answer 1

Presumably, you want the row that has the maximum field1 for each distinct field2. Since you want the entire row, aggregation is not the solution; instead, you want to filter.

Here is one way to do it using a correlated subquery:

select t.*
from mytable t
where t.field1 = (select max(t1.field1) from mytable t1 where t1.field2 = t.field2)

Another option uses window functions:

select *
from (
    select t.*, rank() over(partition by field2 order by field1 desc) rn
    from mytable t
) t
where rn = 1

Finally, here is a more original approach that uses with ties:

select top (1) with ties *
from mytable
order by rank() over(partition by field2 order by field1 desc)