MySQL: How to compare multiple field value of a row and get matched or lowest one?

I have a MySQL table (below):

I can fetch the min or max of the column values simply but my problem is:

1. If any 3 or more fields value matched then fetch that value (if 3 fields values matched more than one value then the lowest of those values), for row #1 2100 will be the output and for row#3 55 will be the output.

2. If no field value matched with a minimum of 3 fields the lowest value of the entire row will be fetched, for row# 2 the output will be 1900.

I can use IF (e.g. select if(col1 = col2, col1, col2) from table) in select but I didn’t find a solution for this situation. Can anyone help to write a MySQL query for this?

Thanks in advance!

Answer

You can use this query to get the results you want. It uses two nested subqueries: the first unpivots the data into a single column; the second then counts how many of each column value occur for a given ID value. The outer query then tests the maximum of the counts; if it is >= 3 then the minimum value which has a count of 3 or more is chosen, otherwise the minimum column value is chosen as the minimum column:

SELECT ID,
       CASE WHEN MAX(cnt) >= 3 THEN MIN(CASE WHEN cnt >= 3 THEN col END)
            ELSE MIN(col)
       END AS min_col
FROM (
  SELECT ID, col, COUNT(col) AS cnt
  FROM (SELECT ID, col1 AS col FROM data
        UNION ALL
        SELECT ID, col2 FROM data
        UNION ALL
        SELECT ID, col3 FROM data
        UNION ALL
        SELECT ID, col4 FROM data
        UNION ALL
        SELECT ID, col5 FROM data
        UNION ALL
        SELECT ID, col6 FROM data
        UNION ALL
        SELECT ID, col7 FROM data
        ) d
  GROUP BY ID, col
  ) dd
GROUP BY ID

Output (for your sample data):

ID  min_col
1   2100
2   1900
3   55

Demo on SQLFiddle