SQL get max value

Question

I have a table called Filter with the following columns:

FilterID    ViewID  Rank1
3              3      1
220           94      1
221           94      2

I need to get the FilterID with a maximum Rank for each ViewID.

The result should be:

FilterID 
3
221

For ViewID 94 Rank1 is 2, hence the value is 221.

Thank you

Answer 1

You didn't state your DBMS so this is ANSI SQL (which works on nearly all modern DBMS)

select FilterID
from (
  select FilterID, 
         ViewID, 
         Rank1,
         max(Rank1) over (partition by ViewID) as max_rank
  from Filter
) t
where Rank1 = max_rank;

Answer 2

In shortcut,

 select max(FilterID)
  from Filter
group by ViewID