Return the first row of a group in MySQL This query gives me the 50 closest addresses around a point:

 SELECT      
     company_has_address.companys_id,     
     (3959 * ACOS(COS(RADIANS(33.2003486)) * COS(RADIANS(geocodes_latitude)) * COS(RADIANS(geocodes_longitude) - RADIANS(-      86.7852465)) + SIN(RADIANS(33.2003486)) * SIN(RADIANS(geocodes_latitude)))) AS distance
 FROM
     geocodes
         LEFT JOIN
     company_has_address ON company_has_address.address_id = geocodes.address_id
 WHERE
     company_has_address.companys_id != 5884
 ORDER BY distance ASC
 LIMIT 50


Now, to make this useful, I need to join two tables: company_has_address, which is essentially a tuple of id, company_id, and address_id,

and then join (again) the company table.

This all works. Kind of.

The problem I have is that I want a list of the 10 closest DISTINCT company names even if a company has more than one address.

So, let's say company ABC has two addresses - a billing address and a shipping address. They are next door to one another. They show up TWICE in the first query. 

Now, prior to MySQL 5.7, I could use GROUP BY to (randomly) pick one of the addresses and it would just give me one result. That worked great. But, now with 5.7, this no longer works, and produces a row for each of the addresses.

Question: how do I filter the first join (company_has_address) to only take the first company_id listed there so that when I join the company table, I can get unique listings? (I don't care if it's the shipping or PO address. They are - geographically - in the same place so which address it chooses is irrelevant).
The next query is not doing exactly what you ask for in your title, but it conceptually does what you're looking for.

The inner SELECT computes the distances for all geocodes (JOIN company_address). The outer SELECT GROUPs BY company_id, gets the "minimum distance", sorts through it; the LIMIT 50 is respected.

SELECT
    companys_id, min(distance) AS distance
FROM
(
    SELECT      
         company_has_address.companys_id,     
         (3959 * ACOS(COS(RADIANS(33.2003486)) * COS(RADIANS(geocodes_latitude)) * COS(RADIANS(geocodes_longitude) - RADIANS(-86.7852465)) + SIN(RADIANS(33.2003486)) * SIN(RADIANS(geocodes_latitude)))) AS distance
    FROM
         geocodes
         LEFT JOIN company_has_address 
             ON company_has_address.address_id = geocodes.address_id
    WHERE
         company_has_address.companys_id != 5884
) AS s0
GROUP BY 
     companys_id
ORDER BY 
     distance ASC
LIMIT 
     50 ;


This query might not be efficient because it will scan your whole geocodes (x company_has_address) tables, but it does the job. 

I would change the LEFT JOIN to a JOIN (does it make sense to have a geocode (address) with no company?).

Check it at http://rextester.com/NCBT28574 



Side note: I actually don't know of any simple way to accelerate your search if the table is big; but you could probably "prefilter" your search within a certain (lat+delta_lat, lng+delta_lng)-(lat-delta_lat, lng-delta_lng) "box", and have proper indexes on lat and lng; this may not be perfect, or "prefilter" too much, but may speed up a lot your queries if the number of points is big enough.



Addendum

If you want to retrieve (lat, lng), use:

SELECT
    companys_id, min(distance) AS distance, geocodes_latitude AS lat, geocodes_longitude AS lng
FROM
(
    SELECT      
         company_has_address.companys_id,     
         (3959 * ACOS(COS(RADIANS(33.2003486)) * COS(RADIANS(geocodes_latitude)) * COS(RADIANS(geocodes_longitude) - RADIANS(-86.7852465)) + SIN(RADIANS(33.2003486)) * SIN(RADIANS(geocodes_latitude)))) AS distance,
         geocodes_latitude, geocodes_longitude
    FROM
         geocodes
         LEFT JOIN company_has_address ON company_has_address.address_id = geocodes.address_id
    WHERE
         company_has_address.companys_id != 5884
) AS s0
GROUP BY companys_id
ORDER BY distance ASC
LIMIT 50 ;


This is non-standard SQL (you cannot mix aggregates with values not GROUPed BY, however, mySQL allows for it, and may give you the (lat, lng) corresponding to the min distance. Other ways of getting to the same place involve the use of CTE (not yet on mySQL, already on MariaDB) or TEMP tables.