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?