JOIN tables, only selecting newest row from second table, and conditional on second table data

Say I have two tables. One has the main details, such as each row is a person and has their name. The second row contains every revision when someone changes "Hair color".

People

Hair colors

Is there a way to LEFT JOIN the two tables so that for each row in People, I only get the newest row for each person in Hair Colors?

So, the result should be:

And then, for the other part of my app, I'd like similar SQL, but on top of selecting the newest row, it also only selects the rows that equal a certain value. For example, only select people where the most recent hair color is Green:

Right now, I just select all people first, then looping through the rows, I check whether the newest hair color row is Green for each person and decide whether to add the person to the final array that way, but it seems that it could be a lot more efficient.

Try this:

with PeopleAllHC as (
	select 
		  P.People_index	
		, P.Name
		, HC.Color
		, HC.Color_change_time
		, Seq=row_number() over (partition by P.People_Index order by HC.Color_change_time desc)
	from 
		People P
		inner join
		HairColors HC
		on P.People_Index=HC.People_Index
)
select 
	  People_index	
	, Name
	, Color
	, Color_change_time
from PeopleAllHC
where Seq=1
and Color='Green'

We get all hair colour changes in the CTE, number the changes from most-recent to oldest using the row_number() over function (this will assign 1 to the most recent) calling it PeopleAllHC. We then select the rows with Seq=1 to limit the output to the latest with the color we want.

(I have changed your table/column names to avoid using delimiters -backticks/brackets/double quotes- ; these are dbms dependent; you may need to adjust them accordingly)