SQL - Query MAX value given MAX value of different column

Question

My table has three columns [ID], [YEAR], [MALES] and there can be multiple MALES values over multiple YEAR values for any given ID.

EX.

[ID]     [YEAR]     [MALES]
1        2010       10
1        2011       20
1        2011       35
1        2011       0
1        2012       25
1        2012       10
2        2010       5
2        2011       2
2        2011       11
2        2011       12
2        2012       0
2        2012       10

I need to query the maximum YEAR and the maximum MALES value for that YEAR for each ID. So the result for the example above would be:

[ID]     [YEAR]     [MALES]
1        2012       25
2        2012       10

Answer 1

You want to do this using row_number():

select id, year, males
from (select t.*,
             row_number() over (partition by id
                                order by years desc, males desc
                               ) as seqnum
      from t
     ) t
where seqnum = 1;

Answer 2

If I understand what you want (your wording is somewhat difficult, but the example makes it look like you want my second one).

Maximum year for each ID:

SELECT id, MAX(year) FROM data;

Maximum males for each ID of maximum year:

SELECT id, year, MAX(males) FROM data WHERE year = (SELECT MAX(year) FROM data) 
    GROUP BY id;

Answer 3

;with CTE as
(
    select
        *,
        row_number() over (partition by ID order by year desc, males desc) as row_num
    from table
)
select *
from CTE
where row_num = 1

or

;with CTE as
(
    select ID, max(Year) as Year
    from table
    group by ID
)
select t.ID, t.Year, max(males) as males
from table as t
    inner join CTE as c on c.ID = t.ID and c.Year = t.Year
group by t.ID, t.year

Answer 4

You need to use a sub-query to get the max year, then join to that to get the max MALES fro that year.

SELECT  T.ID
        ,SQ.MAX_YEAR
        ,MAX(T.MALES)
FROM    TABLE1 T
INNER JOIN
        (
        SELECT  ID
                ,MAX(YEAR) AS MAX_YEAR
        FROM    TABLE1 
        GROUP BY
                ID
        ) SQ
ON      T.ID = SQ.ID
AND     T.YEAR = SQ.MAX_YEAR
GROUP BY
        T.ID
        ,SQ.MAX_YEAR