--- In firebird-support@yahoogroups.com, "smeadsnz" <smeadsnz@...>
wrote:

>
> Hi,
>
> I am having a problem with the firebird sql in that there seems to

be

> no way to group records by only one non-aggregate field. For

example,

> take the following 2 tables:
>
> t1(
> id int pkey,
> code string,
> public_no int
> )
>
> t2(
> id int references t1(id),
> add_date datetime,
> some_value
> primary key(table1_id, add_date)
> )
>
> now, table 2 can have multiple records added for each record of

table

> 1. There seems to be no way to fetch a list of distinct records

from

> table 1 matched to only the latest record of table 2, any join you

do

> will produce a new row for each record of table 2, so you are

getting

> all records instead of the latest.
>
> I'm used to MySql, you'd just need to add a group by clause

containing

> only t1.id and it would work, but Firebird makes you add every

single

> non-aggregate to the group by, making it pointless. Some other

dbms's

> have a First() function which I am sure would do the trick.
>
> Does anybody know of a solution to this?
>

What SQL did you expect to work?

MySQL lets you shoot yourself in the foot in many ways (or at least
did when I last played, Feb 30 was a nice date). It really does not
make sense to not include a non aggregate in a group by because it is
possible to get ambiguous results.

If you honestly don't care about which of the ambiguous other fields
that do not appear in the group by, use min for those fields too.

Or try something like

SELECT *
FROM T1
WHERE ID = (select max(T2.ID) FROM T2)

Adam