php - Mysql查询未按顺序分组

Question

不确定此信息是否足够。我有几个表，我正在运行这个查询，它返回以下结果。

SELECT   CONCAT(pm.fname, " " ,pm.lname) AS fullname,cl.id as clubid,pm.id as player_id,

    ( 3959 * acos( cos( radians(50.82) ) * cos( radians( cm.lat ) )
     * cos( radians( -0.373 ) - radians(-0.373) ) + sin( radians(50.82) )
     * sin( radians( cm.lat ) ) ) ) AS distance

FROM clubmapper AS cm

LEFT OUTER JOIN clubs AS cl ON cl.id = cm.club_id

INNER JOIN playermaster AS pm ON pm.id = cm.usertype_id

LEFT JOIN countries AS co ON co.country_code = pm.nationality

WHERE (pm.fname LIKE 'josephthirtye%' OR pm.lname LIKE 'josephthirtye%')   

#group by cm.usertype_id

ORDER BY distance  ASC,pm.fname ASC, pm.lname ASC
LIMIT 0,12

此查询返回下表:

但我只想要距离最小的第一个结果。我尝试使用分组依据(在查询中注释)，但这给了我最后一行。我只想要该player_id的第一行

还有建议吗？

下面是带有 pretty-print 的相同查询:

更多数据:

我想要距离最小的唯一player_id

Answer 1

这就是我解决问题的方法。谢谢大家的建议。

SELECT   CONCAT(pm.fname, " " ,pm.lname) AS fullname,cl.id as clubid,pm.id as player_id    

FROM (SELECT ( 3959 * acos( cos( radians(50.82) ) * cos( radians( lat ) )
     * cos( radians( -0.373 ) - radians(-0.373) ) + sin( radians(50.82) )
     * sin( radians( lat ) ) ) ) AS distance FROM clubmapper) AS cm

LEFT OUTER JOIN clubs AS cl ON cl.id = cm.club_id

INNER JOIN playermaster AS pm ON pm.id = cm.usertype_id

LEFT JOIN countries AS co ON co.country_code = pm.nationality

WHERE (pm.fname LIKE 'josephthirtye%' OR pm.lname LIKE 'josephthirtye%')   

group by pm.id

ORDER BY distance  ASC,pm.fname ASC, pm.lname ASC

LIMIT 0,12