Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2001

    Unanswered: Tricky SQL, using MAX, no full tablescan

    Let's say I have a table
    SURF, columns

    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

    example data
    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?


  2. #2
    Join Date
    Feb 2001
    NC, USA
    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.

  3. #3
    Join Date
    Jul 2001

    only solution so far

    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.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts