SURF.S_ID is a foreign key to SURF_EVENTS.S_ID.
Indexes are put appropiately.
My goal is to, in one SQL statement,
based on a city name, get the surf id, and the
latest surf event (SE_DESC) based on the last
S_CREATION_DATE for that S_ID
without having full table scans on SURF_EVENTS
If I did this database over again,
I could have had a trigger in SURF_EVENTS
which every time an insert occurred, an extra flag would be set
to 0 for all the old events, and the new event would be flagged
as 1. Then I knew which one would be last one, without using
MAX(SE_CREATION_DATE), but I can't.
Any suggestions on how to construct a SQL without inserting
I don't believe there is any way to avoid using an aggregate function of some sort. This obviously means that your indexes won't be used.
The best you cand do is try to minimize the number of rows that the max(se_creation_date) needs to work on. You can do this by using a correlated query, but then you still have the problem of finding the se_desc which will mean another join to the surf_events table.
How many rows are in this table? You may find that doing a full table scan is actually the most efficient way.