In MariaDB/MySQL, how do I retrieve the same column from different records returned in a single row?

I would like to retrieve a single row that shows the top three colors of products belonging to a specific set. The order is predefined and for some context, the way I would retrieve the top color is below. But I need to show the top three colors in three different fields but in a single row.

SELECT
   color
FROM
   product
WHERE
   category_id = 100
ORDER BY
   FIELD(p.color, 'Red','Orange','Blue','Yellow','Pink','Purple','Brown','Green','Clear','unknown','')
LIMIT 1

I’d like the effect of “LIMIT 3” at the end but I would need a single row, not three rows.

Answer

Retrieve 3 rows and combine the results with GROUP_CONCAT.

SELECT GROUP_CONCAT(color)
FROM (
    SELECT
       DISTINCT color
    FROM
       product
    WHERE
       category_id = 100
    ORDER BY
       FIELD(p.color, 'Red','Orange','Blue','Yellow','Pink','Purple','Brown','Green','Clear','unknown','')
    LIMIT 3
) x

If you need them to be in separate columns in the result, you can put the subqueries in the SELECT list, with different LIMIT clauses.

SELECT (
    SELECT
       DISTINCT color
    FROM
       product
    WHERE
       category_id = 100
    ORDER BY
       FIELD(p.color, 'Red','Orange','Blue','Yellow','Pink','Purple','Brown','Green','Clear','unknown','')
    LIMIT 0, 1) AS first,
    SELECT
       DISTINCT color
    FROM
       product
    WHERE
       category_id = 100
    ORDER BY
       FIELD(p.color, 'Red','Orange','Blue','Yellow','Pink','Purple','Brown','Green','Clear','unknown','')
    LIMIT 1, 1) AS second,
    SELECT
       DISTINCT color
    FROM
       product
    WHERE
       category_id = 100
    ORDER BY
       FIELD(p.color, 'Red','Orange','Blue','Yellow','Pink','Purple','Brown','Green','Clear','unknown','')
    LIMIT 2, 1) AS third