mysql order by子查询_来自子查询的MySQL ORDER BY由GROUP BY丢失(MySQL ORDER BY from subquery lost by GROUP BY)...

I have a table x :

id       lang        externalid

1        nl            10

2        nl            11

3        fr             10

From this table I want al the rows for a certain lang and externalid, if the externalid doesn't exist for this lang, I want the row with any other lang.

The subquery sorts the table correct, but when I add the group by, the sort of the subquery is lost. This works in older mysql versions but not in 5.7.

(

SELECT

*

FROM

x

ORDER BY FIELD(lang, "fr") DESC, id

)

as y

group by externalid

I want the query to return the records with id 2 & 3. So for each distinct external id, if possible the lang = 'fr', else any other lang.

How can i solve this problem?