[miserableman]

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

[andrewst]

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:

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:

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.

[r937]

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)

[miserableman]

Thanks alot, I appreciate the replies. I have a question about this SQL though:

Originally Posted by andrewst

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

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.

[andrewst]

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):

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;