At 12:30 PM 9/08/2006, you 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.

A grouped query *is* an aggregation mechanism. It takes an output
set that has distinct groupings defined by the input sets and applies
some aggregating function to a column that does *not* contain common
values. Aggregating functions are max(), min(), sum(), avg() and
count(). That's what GROUP BY is for - gathering up the
commonalities between rows in output sets.

> 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) <-- not valid, assume you mean 'id'
>)
>
>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.

Your understanding of aggregation seems rather vague. Sure, your
join is potentially going to output a denormalised set. It is that
set to which you apply the aggregation.

Suppose you wanted to perform a query returning the date of the most
recent t2 record for each t1 code value:

select
t1.id,
t1.code,
t1.public_no,
max(t2.add_date)
from t1
join t2 on
t2.id = t1.id
group by 1, 2, 3

Clearly you can't throw the column t2.some_value into this mix,
because it is row-specific, i.e. non-aggregating data.

So, if you want to aggregate AND return non-aggregate data in your
final output, of course you are going to have one row of output for
each instance of the join if you don't provide a limiting WHERE
clause. You can still fetch the aggregated value, but you do it
using a conditioning subquery expression in the WHERE clause.

e.g.

select
t1der.id,
t1der.code,
t1der.public_no,
t2a.some_value,
t2a.add_date
from t1 t1der
join t2a t2 on
t2a.id = t1der.id
where t2a.add_date = (select max(t2b.add_date)
from t2 t2b
where t2b.id = t1der.id)

>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.

Pointless for what? compliance with MySQL's famous logic standards?

>Some other dbms's have a First() function which I am sure would do the trick.

First is available, but it doesn't return a result for each primary
value, only the first row in the entire output. So, though there
could be arbitrary conditions where you could get identical results
from an aggregation or a SELECT FIRST 1, FIRST is not an aggregating
mechanism.

select first 1
t1.id,
t1.code,
t1.public_no,
t2.some_value,
t2.add_date
from t1
join t2a t2 on
t2a.id = t1der.id
order by t2.add_date desc

There are also rather a lot of things that you *can* do with SELECT
FIRST that you shouldn't do, but that's another chapter....

./heLen