Select Distinct Top One Record by Date

I am writing a script to compare software between servers but have a many-to-many relational situation to execute this cleanly. I’m thinking a distinct-top by latest date type of query may help. So that I can go from this

To this shortened and clean list

I am not exactly sure how to approach this? Should I start with a sort, then top distinct by date?

Answer

You can also try using row_number()

select * from
(
select *, row_number() over(partition by software_list order by date desc) as rn
from tablename
)A where rn=1