Let's say I have a table
SURF, columns
S_ID, S_DESC,S_CITY

example data:
1, 'fun wave','Hermosa'
2, 'big wave'.'Maverick'
2, 'micro wave'.'Hermosak'

and an event table for each of this
surf occassions.

SURF_EVENTS, columns
SE_ID, S_ID, SE_CREATION_DATE,SE_DESC

example data
1,1,2001-01-01,'booked'
2,1,2001-01-02,'mailed'
3,1,2001-01-03,'cancelled'
4,2,2001-01-01,'booked'
5,3,2001-02-01,'water bad'



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
a trigger?

Regards,
/latompa

Hmm. that is a tricky one

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.

Let us know what you come up with.

My only solution here is to have a integer column called
SE_LATEST, which is either 1 or NULL.

I can have an index on SE_S_ID and SE_LATEST,
By doing this, the index won't be too large, since NULL values
won't be indexed.

So when I join on SE_S_ID AND S_ID and use SE_LATEST=1,
I'll use the index and don't care about older SURF_EVENTS.

The con here is that I need to modify the code which inserts
rows in the table, or add a trigger.

/latompa