Select first row in each GROUP BY group in mySQL and Laravel?

Question

As the title suggests, I'd like to select the first row of each set of rows grouped with a GROUP BY.

Specifically, if I've got a user_analytics table that looks like this:

SELECT latitude, longitude, country_name, code, COUNT(*) as total_visitors FROM referral_analytics
WHERE created_at BETWEEN '2020-01-03'AND '2021-01-03'
GROUP BY code,country_name,latitude,longitude

My Output:

latitude  | longitude  | country_name              | code| total_visitors
----------+------------+---------------------------+-----+---------------
32.548328 | -92.045235  | United States of America | US  | 1
33.389011 | -111.844017 | United States of America | US  | 1
34.060734 | -118.239738 | United States of America | US  | 1
38.935699 | -77.3508    | United States of America | US  | 1
39.613087 | -104.883926 | United States of America | US  | 1
39.751099 | -104.997101 | United States of America | US  | 1
39.772247 | -86.156517  | United States of America | US  | 1
39.966381 | -83.012772  | United States of America | US  | 4

I'd like to query the first or min latitude and longitude for each group in MySQL/ Laravel query. Something like this:

Expected Output:

latitude  | longitude  | country_name              | code| total_visitors
----------+------------+---------------------------+-----+---------------
32.548328 | -92.045235  | United States of America | US  | 11

Note that grouping by the float latitude and longitude values might not work as you expect, as these values are not _exact_. — Tim Biegeleisen, Apr 16 '21 at 08:47
Are you saying you want to group by just country name and get total visitors as well as an arbitrary row belonging to that group? In that case then take a look at something like https://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group-mysql — apokryfos, Apr 16 '21 at 09:10

score -1 · Answer 1 · answered Apr 16 '21 at 08:55

-1

Got this -

SELECT MIN(user_latitude) as latitude, MIN(user_longitude) as longitude, user_country_name, user_country_code, COUNT(*) as total_visitors
        FROM referral_analytics
        WHERE created_at BETWEEN '2020-01-03'AND '2021-01-03'
        GROUP BY user_country_code,user_country_name

answered Apr 16 '21 at 08:55

Masudul Hasan Shawon

502
3
14

Selecting a coordinate closest to 0,0 seems like an odd objective – Strawberry Apr 16 '21 at 08:57
Note this will select the smallest latitude and longitude but will not select the smallest pair of lon-lat so if you had one entry with `0, 10` and another entry with `10, 0` this would end up selecting `0,0` which are not coordinates that exist on your database together – apokryfos Apr 16 '21 at 08:59

Select first row in each GROUP BY group in mySQL and Laravel?

1 Answers1