MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();


version()


8.0.26


-- red,orange,yellow,green,blue,indigo,violet
/*
PERSON, COLOR
John, orange
John, blue
Jack, green
Jack, purple
*/

WITH data0 (person, color) AS (
         SELECT 'John', 'orange' UNION
         SELECT 'John', 'blue'   UNION
         SELECT 'Jack', 'green'  UNION
         SELECT 'Jack', 'purple'
     )
   , data1 AS (
         SELECT t.*
              , FIND_IN_SET(color, 'red,orange,yellow,green,blue,indigo,violet') AS seq
           FROM data0 AS t
     )
   , data AS (
         SELECT t.*
              , ROW_NUMBER() OVER (PARTITION BY person ORDER BY seq < 1, seq) AS n
           FROM data1 AS t
     )
SELECT person, color, seq, n
  FROM data
 WHERE n = 1
;


person
color
seq
n


Jack
green
4
1

John
orange
2
1


WITH data0 (person, color) AS (
         SELECT 'John', 'orange' UNION
         SELECT 'John', 'blue'   UNION
         SELECT 'Jack', 'green'  UNION
         SELECT 'Jack', 'purple'
     )
   , data1 AS (
         SELECT t.*
              , FIELD(color, 'red','orange','yellow','green','blue','indigo','violet') AS seq
           FROM data0 AS t
     )
   , data AS (
         SELECT t.*
              , ROW_NUMBER() OVER (PARTITION BY person ORDER BY seq < 1, seq) AS n
           FROM data1 AS t
     )
SELECT person, color, seq, n
  FROM data
 WHERE n = 1
;


person
color
seq
n


Jack
green
4
1

John
orange
2
1