http://sqlfiddle.com/#!9/857486/3 Do you see my setup code etc? (I haven't used any fiddles before). I DO notice that on JSFIDDLE when using this code, i get the LATESt date for colony 8. (17th). but on my main website MYSQL (shared server) I'm getting the16th for the same data & sane SQL command. SELECT * FROM (SELECT * FROM `dailycolonystats` ORDER BY `Date` DESC) as sub WHERE `Region` = 'AUCKLAND' GROUP BY Grp, Colony, Type; Only colony 8 has 2 sets of dates - The rest have 1 set of dates / figures so far... My main shared server has this version - SELECT VERSION() = 5.6.47-87.0-log On Fri, 26 Aug 2022 at 12:34, Aziz Saleh <azizsaleh@xxxxxxxxx> wrote: > > Can you add the schema and sample inserts here and send a link (makes it easier to debug on the run): http://sqlfiddle.com > > My assumption is that you want to do the grouping in the inside select, but there might be a better way of doing it if you can give a sample DDL/inserts to play around with. > > On Thu, Aug 25, 2022 at 4:49 PM <gordonisnz@xxxxxxxxx> wrote: >> >> 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. -- Gordon. -- Gordon.
