[Solved] MAX() ... GROUPED BY misbehaviour

[FDB]

OSX 10.14.4 (Mojave)
OpenOffice 4.1.14 (BASE component)

Fot table T1 :

Code: Select all

ID  NAME   ORG      DATE          VALUE
2    eee   ORG_01   28 Feb 1996   1002
1    ddd   ORG_01   27 Nov 1998   1001
3    fff   ORG_01   27 Apr 2001   1003
6    eee   ORG_02   22 Oct 1971   1006
5    ddd   ORG_02   05 Apr 1973   1005
7    fff   ORG_02   21 Feb 1975   1007
8    ggg   ORG_02   20 Mar 1994   1008
4    ccc   ORG_02   10 Mar 2022   1004
10   eee   ORG_03   30 Apr 2005   1010
9    ddd   ORG_03   09 Jun 2006   1009
11   fff   ORG_03   22 May 2009   1011
14   eee   ORG_04   03 Dec 1984   1014
13   ddd   ORG_04   28 Oct 2011   1013
12   aaa   ORG_04   01 Jan 2050   1012
15   iii   ORG_04   02 Jan 2050   1015

I have a view v1T1 :

Code: Select all

SELECT  "T1"."ID","T1"."NAME","T1"."ORG","T1"."DATE","T1"."VALUE"  
FROM "T1"
WHERE "T1"."DATE" IN (
	SELECT MAX("T1"."DATE")
	FROM "T1"
	GROUP BY "T1"."ORG"
)

resulting in :

Code: Select all

ID  NAME   ORG      DATE          VALUE
3    fff   ORG_01   27 Apr 2001   1003
4    ccc   ORG_02   10 Mar 2022   1004
11   fff   ORG_03   22 May 2009   1011
15   iii   ORG_04   02 Jan 2050   1015

which is ok (finding the record with the latest date for each ORG)

However when I add the record :

Code: Select all

16   aaa   ORG_03   01 Jan 2050   1016

(this DATE is also a date in group ORG_04)

group ORG_04 seem to split into 2 groups :

Code: Select all

ID  NAME   ORG      DATE          VALUE
3    fff   ORG_01   27 Apr 2001   1003
4    ccc   ORG_02   10 Mar 2022   1004
12   aaa   ORG_04   01 Jan 2050   1012
15   iii   ORG_04   02 Jan 2050   1015
16   aaa   ORF_03   01 Jan 2050   1016

This is not correct anymore.

Is the SQL script not correct ?
Or is this a fluke in the database engine ? Possible workaround ?

advTHANKSance

[chrisb]

you use:
WHERE "T1"."DATE" IN

so if the date 01 Jan 2050 is in the table T1 then that record is found and selected.

your sql does exactly as asked.

the recommended way to do this when using HSQLDB is:

Code: Select all

select
	"T1"."ID", "T1"."NAME", "T1"."ORG", "T1"."DATE", "T1"."VALUE"  
from
	(select "ORG", max("DATE") "MX_DATE" from "T1" group by "ORG") "A"
join
	"T1" on "A"."ORG" = "T1"."ORG" and "A"."MX_DATE" = "T1"."DATE"

[FDB]

THX for the not so simple SQL