MySQL error using DISTINCT query and ORDER BY

Question

I'm trying to perform a DISTICNT query on one column and sort by another column that is not in a SELECT statement. I get this error:

Query failed SQLSTATE[HY000]: General error: 3065 Expression #1 ORDER BY clause not in SELECT list, referenced column 'midnites_midNite.product_in_category.sortOrder' is not present Selection list; this is not compatible with DISTINCT

I have a products_in_category example table with four columns: ID, cat_ID, product_ID and sortOrder. I have been trying to perform a Product_ID WHERE cat_ID = 9 DISTICNT query and sort by the associated query value in the sortOrder column.

ID      cat_ID  product_ID  sortOrder
+----------+--------+-----------+--------+
|    1     |    9   |     5     |    3   |
+----------+--------+-----------+--------+
|    2     |    9   |     26    |    1   |
+----------+--------+-----------+--------+
|    3     |    9   |     5     |    2   |
+----------+--------+-----------+--------+
|    4     |    9   |     7     |    4   |
+----------+--------+-----------+--------+
|    5     |    9   |     5     |    5   |
+----------+--------+-----------+--------+
|    6     |   22   |     4     |    6   |
+----------+--------+-----------+--------+

SELECT DISTINCT product_ID 
FROM product_in_category 
WHERE cat_ID = 9

This query returns 3 values ​​5, 26, 7, which is correct, but I also need to sort by their associated sortOrder value. If I use this query below, it returns 5 values, which is incorrect because all values ​​in the sortOrder column are unique.

SELECT DISTINCT product_ID, 
                sortOrder 
FROM product_in_category 
WHERE cat_ID = 9 
ORDER BY sortOrder

If I use the query below, mySQL throws this error and I understand why, sortOrder is not included in the SELECT statement. I'm not sure how to perform a DISTICNT query on the Product_ID column and sort by the associated sortOrder value of the returned value.

Query failed SQLSTATE[HY000]: General error: 3065 Expression #1 ORDER BY clause not in SELECT list, referenced column 'midnites_midNite.product_in_category.sortOrder' is not present Selection list; this is not compatible with DISTINCT

SELECT DISTINCT product_ID 
FROM product_in_category 
WHERE cat_ID = 9
ORDER BY sortOrder

I have tried various union, join and select statements in various ways. I hope this is understandable and appreciate any help you can provide! ! !

Answer 1

SELECT product_ID, MIN(sortOrder) AS minSortOrder
    FROM product_in_category
    WHERE cat_ID = 9
    GROUP BY product_ID;