Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2007
    Posts
    2

    How to optimize this design?

    Hey everyone,

    I've got a very simple database with a single table:

    EVENT_TABLE
    -------------
    description
    start_date
    Now what I want to do with the contents of this table is display the latest(events with start_dates furthest in the future) N dates on which events occur. For example, with N=2 and assuming there's some data in the table I would like to output something similar to the following:

    Wednesday, September 26, 2007
    --------------------------------
    1. event description
    2. event description
    3. event description


    Monday, September 24, 2007
    --------------------------------
    1. event description
    2. insert event description
    Now I need to
    1) somehow figure out what the most recent dates with events are
    2) for each of those dates get the events and display them

    Seems like a lot of queries would need to be made to accomplish such a seemingly simple task... I'm quite new to the db world and would appreciate any thoughts on how I could improve this design

    Thanks

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    This question really belongs in the SQL forum (assuming you are using SQL?) or the appropriate DBMS forum since the exact SQL to do this will depend on your DBMS. Some have constructs like "TOP(N)" or "ROWNUM <= N" to get the first N rows from a query. The SQL I have shown below is less efficient but should work on any DBMS:

    1) To get the latest N dates in the table:

    Code:
    select distinct start_date
    from   events e1
    where  N > (select count(distinct start_date) 
                from   events e2
                where  e2.start_date > e1.start_date
               );
    NOTE: I am assuming start_date is a pure date with no time component. If it includes a time component you need to remove that - e.g. in Oracle you would use TRUNC(start_date).

    2) To get all the data for those dates:

    Code:
    select *
    from   events
    where  event_date in
    ( select distinct start_date
      from   events e1
      where  N > (select count(distinct start_date) 
                  from   events e2
                  where  e2.start_date > e1.start_date
                 )
    );
    Again, you'll need to ignore time components if they exist.

    You only need query (2): I just showed query (1) to explain how the method works.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    and to answer your original question, "How to optimize this design?" -- you can't, it's as optimal as it can be

    the only thing that's up for debate is whether your PK is description (each event may occur only once), start_date (each date may have only one event), or the composite {description,start_date} (each event may occur only once on each date)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Sep 2007
    Posts
    2
    Thanks alot, I appreciate the replies. I have a question about this SQL though:

    Quote Originally Posted by andrewst
    1) To get the latest N dates in the table:

    Code:
    select distinct start_date
    from   events e1
    where  N > (select count(distinct start_date) 
                from   events e2
                where  e2.start_date > e1.start_date
               );
    How exactly does this work? The subquery with "e2.start_date > e1.start_date" is especially confusing to me.

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    It's called a "correlated subquery", which means it is evaluated for each row of the main query. It works a bit like this (conceptually):
    Code:
    for e1 in (select distinct start_date from event)
    loop
      select count(distinct start_date) 
      into cnt
      from   events e2
      where  e2.start_date > e1.start_date;
      if N > cnt then
        output e1.start_date;
      end if;
    end loop;

Posting Permissions

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