Hello. I've got a pickle with the MYSQLI GROUP BY and wondering if anyone can assist. basically, I've got a small report/query that extracts data, but then I go into a foreach loop & "manually" extract only the latest / most recent data out of it, & create an array. This seems to work. I'm now on a REGION report 9extracts a lot more data, once things are in place), so I would rather the SQL command be correct to extract only the latest data in the first instance - rather than manually filtering out the data. I've got a table with the following fields - Region - Group (number) - Colony (number) - type (cats or kittens) - date (yyyymmdd) - number cats (a number - number of cats/kittens in that colony) - number kittens (a number - number of cats/kittens in that colony) My current / latest SQL is: SELECT * FROM (SELECT * FROM `dailycolonystats` ORDER BY `Date` DESC) as sub WHERE `Region` = 'AUCKLAND' GROUP BY Grp, Colony, Type ;" It seems to be working OK, EXCEPT the total is wrong. If I have 3 rows of information CITY, 999, 1, cats, 20220815, 5 CITY, 999, 3, cats, 20220815, 7 CITY, 999, 3, cats, 20220818, 5 Colony 3, had 7 kittens on 20220815 (15 Aug 2022). However, on the 18th, it dropped to 5. The results I'm getting include the 15th of august for colony 3. and not the 18th. I've been working on this for the last few days and it's annoying me. Is there a way to alter the SQL to Group by the city/group/colony number, and type? Then Sort by date DESCENDING and only use the latest date for each group. (i know the GROUP BY uses the first result it sees) PS - I can't sort by date first, as, in the above example, Colony 3 has 18th August, but colony 1 doesn't have an entry of that date. Any advice? -- Gordon.
