Can you try this query (might be a more efficient way to do it):
SELECT dcs.* FROM dailycolonystats dcs,
(
SELECT Grp, Colony, Type, Max(Date) AS date FROM `dailycolonystats`
GROUP BY Grp, Colony, Type
) as max_dates
WHERE dcs.Region = 'AUCKLAND'
AND dcs.date = max_dates.date
AND dcs.Grp = max_dates.Grp
AND dcs.Colony = max_dates.Colony
AND dcs.Type = max_dates.Type
(
SELECT Grp, Colony, Type, Max(Date) AS date FROM `dailycolonystats`
GROUP BY Grp, Colony, Type
) as max_dates
WHERE dcs.Region = 'AUCKLAND'
AND dcs.date = max_dates.date
AND dcs.Grp = max_dates.Grp
AND dcs.Colony = max_dates.Colony
AND dcs.Type = max_dates.Type
On Fri, Aug 26, 2022 at 1:17 AM <gordonisnz@xxxxxxxxx> wrote:
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.
