mysql select column outside group by

Question

我有以下数据集

ID  P   C1    C2    XR  
1   1   GBP   USD   1.6
2   2   GBP   USD   1.7
3   1   GBP   EUR   0.84
4   2   GBP   EUR   0.83
5   1   GBP   CHF   1.8
6   2   GBP   CHF   1.8

我正在尝试获得每对的最小 XR，因此结果应该是

ID  P   C1    C2    XR  
1   1   GBP   USD   1.6
4   2   GBP   EUR   0.83
5   1   GBP   CHF   1.8

但是当我 SELECT MIN(XR) GROUP BY C1,C2 时，我要么没有得到正确的 ID，要么收到一条警告说 ID not in GROUP BY if 'ONLY_FULL_GROUP_BY' enabled

我得到的最接近的是:

SELECT t1.id, t1.C1, t1.C2, MIN(t2.XR) FROM xrates t1 
LEFT JOIN xrates AS t2 ON t1.id = t2.id
GROUP BY t1.id, t1.C1, t1.C2
HAVING t1.C1 = 'GBP' AND t1.C2 IN ('USD','EUR')

Answer 1

select rates.*
from xrates rates
where id in 
(
    select min(xrates.id) as minid    
    from xrates
    inner join
    (
        select C1, C2, MIN(XR) as minxr
        from xrates
        GROUP BY C1, C2
        HAVING C1 = 'GBP'
    ) x on x.c1 = xrates.c1 and x.c2 = xrates.c2 and x.minxr = xrates.xr
    group by xrates.C1, xrates.C2
)

SQLFiddle demo