sql - learn row_number() with case when

sql.sql

select

ranked = case
	when color <> 'black' then
		row_number() over (partition by
			case when color <> 'black' then 1 end			
			,person
			order by (select null)
		)
	else null
end

,*

from (
	values
		(1, 'kevin', 'blue')
		,(2, 'kevin', 'black')
		,(3, 'owen', 'blue')
		,(4, 'aria', 'red')
		,(5, 'aria', 'pink')
		,(6, 'wendy', 'orange')
		,(7, 'wendy', 'green')
) favoriteColors (id,person,color)

order by person, color

/*
ranked               id          person color
-------------------- ----------- ------ ------
1                    5           aria   pink
2                    4           aria   red
NULL                 2           kevin  black
1                    1           kevin  blue
1                    3           owen   blue
1                    7           wendy  green
2                    6           wendy  orange

(7 row(s) affected)


*/