DISTINCT query and ORDER BY throws mySQL error-CodePudding

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

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

I have a products_in_category sample table that has four columns, ID, cat_ID, product_ID and sortOrder. I've been trying to do a DISTICNT query on product_ID WHERE cat_ID = 9 and sort by the query values associated 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 need to sort by their associated sortOrder values as well. If I use this query below it returns 5 values which is incorrect because all the 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 the query below mySQL throws this error and I understand why, sortOrder wasn't included in the SELECT statement. I am not sure how to do a DISTICNT query on the product_ID column and sort by the return value's associated sortOrder values.

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

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

I've tried various union, join and select statements in various ways. I hope this is understand able and appreciate any help you can provide!!!

CodePudding user response：

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