How to get max year from a column and keep the rest?

Question

Sorry if it's obvious but I'm really new to SQL. My data looks like this

Policy	Email	Years
Pol1	John@gmail	1
Pol1	John@gmail	1
Pol1	Jo@aol.	5
Pol1	Jo@aol.	6

How do I get it to pull the data for each policy, but only the ones with the highest years for each policy. So rows 2 and 4

Answer 1

A correlated subquery is a typical method:

select t.*
from t
where t.year = (select max(t2.year) from t t2 where t2.policy = t.policy);

Answer 2

SELECT Policy,max(Year) FROM TABLE GROUP BY Policy

This query would group the records by Policy and Select MAX of year from each group. If you need only Policy and Year then the above query should be fine. Because you can not select any other columns in the group by query.

If you want other columns as well in the result, then you can try something like the below query.

SELECT 
 T1.* 
FROM 
 TABLE T1 
INNER JOIN 
 ( SELECT Policy,max(Year) as [MaxYear] FROM TABLE GROUP BY Policy ) T2
ON 
 T1.Polcy = T2.Policy AND T1.Year = T2.MaxYear

Answer 3

I can't see what version of SQL you're using but, inspired by this post: SQL Query to get column values that correspond with MAX value of another column?

I wrote this:

select  core.policy, 
        core.email, 
        core.years
From ExamplePolicies core
Join (SELECT    maxYears = MAX(years),
                policy
      FROM ExamplePolicies 
      GROUP BY policy) subq
      on core.years = subq.maxYears

Tested here:

https://sqliteonline.com/