Doing a top N after self join

From: Jake <me_at_heyjay.com>
Date: Wed, 6 Aug 2003 10:41:57 -0500
Message-ID: <bgr7kp$eq0$1@bob.news.rcn.net>

Hi, I have a table with an enddate, and I want to join each row with the row one day ago (i.e. yesterday with respect to the current row), then I want to return the top N days.

I'd like to do it like:

select *
  from (

        /* join today and yesterday */
        select today.*, yesterday.*
          from (
                /* assign a daynum after sorting data */
                select rownum daynum, inner.*
                  from (
                        /* sort all the data */
                        select *
                          from mytable
                         order by enddate
                       ) inner


               ) today,

               (
                select rownum daynum, inner2.*
                  from (
                        select *
                          from mytable
                         order by enddate
                       ) inner2


               ) yesterday

         where yesterday.daynum = today.daynum - 1
         order by today.daynum desc
      )

  where rownum < 100

My problem is after I do the join and sort desc, my columns are ambiously defined. I'd perfer not to have to alias every column from each table.

Is there a way to access the today.* and yesterday.* columns?

thanks
Jake Received on Wed Aug 06 2003 - 10:41:57 CDT