Selecting rows - unique field as criteria

Question

Having this table:

Row    Pos      Outdata    Mismatch          Other
1        10       S           0               A
2        10       S           5               A
3        10       R           0               B
4        10       R           7               B
5        20       24          0               A
6        20       24          5               B
6        20       32          10              C

How can I select all rows where Pos=10 having unique Outdata. If more than one row exists, I would like to have the row where the field Mismatch is smallest. Ie I would like to get rows 1 and 3, not 2 and 4.

In that select I would also like to do the same for all Pos=20, so the total result should be rows 1,3,5,6

(And I want to then access the "Other" field, so I cant only SELECT DISTINCT on Pos and OutData and Mismatch).

Is there a query to do this in MySQL?

Answer 1

Try this:

Select * From Table ot
Where pos = 10 
   And MisMatch = 
       (Select Min(MisMatch) From Table
        Where pos = 10
           And Outdata = ot.OutData)

Answer 2

Rationale is to create a table with all values of Pos, OutData and the lowest Mismatch and use the combination of these fields as a unique key into your actual table.

SELECT    t1.*
FROM      MyTable t1
          INNER JOIN (
            SELECT    Pos, OutData, Mismatch = MIN(Mismatch)
            FROM      MyTable
            GROUP BY  Pos, OutData
          ) t2 ON t2.Pos = t1.Pos
                  AND t2.OutData = t1.OutData
                  AND t2.Mismatch = t1.Mismatch

Answer 3

This should work for you:

 SELECT * 
 FROM table T1 
 GROUP BY Pos, Outdata 
 HAVING Mismatch = (
     SELECT MIN(Mismatch)
     FROM table T2
     WHERE Pos = T1.Pos AND
     Outdata = T1.Outdata
 )

Answer 4

Here I am assuming that (Pos, OutData, Mismatch) is not unique, but that (Row, Pos, OutData, Mismatch) is unique:

SELECT T3.*
FROM Codes T3
JOIN (
    SELECT MIN(Row) AS Row
    FROM (
        SELECT Pos, OutData, Min(Mismatch) AS Mismatch
        FROM Codes
        GROUP BY Pos, OutData
    ) T1
    JOIN Codes T2
    ON T1.Pos = T2.Pos AND T2.OutData = T2.Outdata AND T1.Mismatch = T2.Mismatch
    GROUP BY T2.Pos, T2.OutData, T2.Mismatch
) T4
ON T3.Row = T4.Row

Result:

1, 10, 'S', 0, 'A'
3, 10, 'R', 0, 'B'
5, 20, '24', 0, 'A'
7, 20, '32', 10, 'C'

Note that I have also corrected the second row 6 to become row 7, as I believe that this was a mistake in the question.