On Mar 20, 8:28 pm, "Bob Bedford" <b...@bedford.com> wrote:
> add this to the script:
> INSERT INTO `folderstatus` VALUES ('14', '3', '5');
> update `typestatus` set idcheck = 0 where idtypestatus = 5;
>
> Now the query:
> select folder.idperson, folderstatus.idtypestatus,typestatus.idcheck,
> folderstatus.idfolderstatus,
> folder.datefolder, folder.idfolder
> from typestatus
> inner join folderstatus on typestatus.idtypestatus =
> folderstatus.idtypestatus
> inner join folder on folderstatus.idfolder = folder.idfolder
> inner join person on folder.idperson = person.idperson
> inner join client on folder.idclient = client.idclient
> where typestatus.idcheck = 0
> group by idtypestatus, idfolder
> having (idfolderstatus = max(folderstatus.idfolderstatus))
>
> I've 4 results but IdFolder 3 is shown twice and I only want the bigger
> idfolderstatus for every folder.
>
> How to do so ? I seem close to the final result, I've tried to group by
> different manners but no way. Something's wrong and I'm not a MySQL gourou,
> so please help me.
>
> Bob
Well, you must be using a different version of google! I get 66 hits
in this ng!

Anyway, here's an example of what I'm talking about. Incidentally,
this is an example of a groupwise max problem.
You may find it helpful to include fs2.* in the SELECT part of the
query in order to better understand what's going on.
I've tried to use your naming convention but I probably screwed up
somewhere. The GROUP BY part of this statement is a bit of a
nonsense. It just says 'In the event of a tie, pick one. I don't care
which.' In reality you'd probably want to handle ties a little more
thoughtfully:

SELECT fs1 . *
FROM `folderstatus` fs1
LEFT JOIN folderstatus fs2 ON fs2.idfolderstatus <> fs1.idfolderstatus
AND fs1.idfolder = fs2.idfolder
AND fs2.idtypestatus > fs1.idtypestatus
WHERE isnull( fs2.idtypestatus )
GROUP BY fs1.idfolder
ORDER BY fs1.idfolder

> Anyway, here's an example of what I'm talking about. Incidentally,
> this is an example of a groupwise max problem.
> You may find it helpful to include fs2.* in the SELECT part of the
> query in order to better understand what's going on.
> I've tried to use your naming convention but I probably screwed up
> somewhere. The GROUP BY part of this statement is a bit of a
> nonsense. It just says 'In the event of a tie, pick one. I don't care
> which.' In reality you'd probably want to handle ties a little more
> thoughtfully:
>
> SELECT fs1 . *
> FROM `folderstatus` fs1
> LEFT JOIN folderstatus fs2 ON fs2.idfolderstatus <> fs1.idfolderstatus
> AND fs1.idfolder = fs2.idfolder
> AND fs2.idtypestatus > fs1.idtypestatus
> WHERE isnull( fs2.idtypestatus )
> GROUP BY fs1.idfolder
> ORDER BY fs1.idfolder

Great it works !

Now the pain will be to transpose to my DB with at least 20 linked
table...but I got the idea.

Thanks for your help.

Bob