Hi John!

Theoretical questions are normally quite simple:

select b.Genre, b.ID, b.Title, b.Author
from books b
where not exists(select * from books b1
where b1.Genre = b.Genre
and b1.DateOfSubmission > b.DateOfSubmission)

This will, of course, give you several books in the same Genre if the
DateOfSubmission is the same. Such problems are normally easily
avoided by comparing a unique value in case of duplicates, e.g.

select b.Genre, b.ID, b.Title, b.Author
from books b
where not exists(select * from books b1
where b1.Genre = b.Genre
and ((b1.DateOfSubmission > b.DateOfSubmission) or
(b1.DateOfSubmission = b.DateOfSubmission and
b1.ID > b.ID))

This should help you get started,
Set

--- In firebird-support@yahoogroups.com, John vd Waeter wrote:
> Hi all,
>
> Problem simplified:
>
> I have a table BOOKS, 5 fields:
> 1. ID
> 2. Genre
> 3. Title
> 4. Author
> 5. DateOfSubmission
>
> The table contains bookdata (obvious).
> Now I would like to get an aggregate that, for everyGenre, shows me
> the Titles and Authors of the Latest submitted book. If there are 5
> Genres, I should get a 5 row table.
>
> In metacode: For every Genre, find the latest DateOfSubmission, and
> give me ID, Author and Title.
>
> It looks like a simple SQL, but I'm a bit stuck on the "for every
> Genre".
>
> Can anyone give me a hint?
>
> tia!
> John