Re: MYSQLI and GROUP BY help - please

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

On Fri, Aug 26, 2022 at 1:17 AM <[email protected]> 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 <[email protected]> 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 <[email protected]> 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.
>