Re: cycled order

From: Chris Weiss <weisschr_at_pilot.msu.edu>
Date: Thu, 27 May 1999 16:03:54 -0400
Message-ID: <7ik8fl$o34$1@msunews.cl.msu.edu>

If understand your question correctly, then I think you need two queries. I wrote a little test table

You need dates ordered in a year by their relative position to today's date, right? You had an error in your original post if this is the case.

Assuming the day of the year is May 2:

SQLWKS> SELECT * FROM C;
A

---

01-MAY-70
02-MAY-80
03-MAY-90
04-MAY-70
10-JAN-99

5 rows selected.

SQLWKS> SELECT * FROM C
     2> WHERE TO_CHAR(A,'MON-DD') >= 'MAY-02'
     3> ORDER BY TO_CHAR(A,'MON-DD-YYYY');

A

---

02-MAY-80
03-MAY-90
04-MAY-70

3 rows selected.

SQLWKS> SELECT * FROM C
     2> WHERE TO_CHAR(A,'MON-DD') < 'MAY-02'
     3> ORDER BY TO_CHAR(A,'MON-DD-YYYY');

A

---

10-JAN-99
01-MAY-70
2 rows selected.

If you play with the echo, headers, etc. You can make these queries look as if they were a single statement.

Christopher Weiss
Professional Services Division
Compuware Corporation

Jerry Gitomer <jgitomer_at_hbsrx.com> wrote in message news:7ik79m$7hv$5_at_autumn.news.rcn.net...
> Hi Fred,
>
> Here's a really ugly answer.
>
> SELECT my_date
> FROM my_table
> ORDER BY TO_CHAR(my_date,'YYYY-MM-DD');
>
> regards
>
> Jerry Gitomer
> ----------------------------------------------------
>
> Fred Ruffet wrote in message <7ijr8h$fr4$1_at_gatekeeper.ornano.kapt.com>...
> >Hi everyone,
> >
> >Here's a little problem. I have a table T with a key K, a name N and a
> >birthdate D. I would like to do a select that would give me the list of
all
> >people ordered by birthdate in the year (I mean that 2-jan-1960 is after
> >1-jan-1960, and also after 1-jan-1970). This list would begin on the
> today's
> >date.
> >
> >if I have this list :
> >
> >01-sep-1960
> >15-aug-1964
> >17-jan-1968
> >23-may-1974
> >24-nov-1974
> >
> >and we are the 27 april, it would return :
> >
> >15-aug-1964
> >01-sep-1960
> >24-nov-1974
> >17-jan-1968
> >23-may-1974
> >
> >Pretty, isn't it ?
> >Thanks for any help,
> >
> >Fred
> >
> >"It's not because you're paranoid that they're not after you."
> >
> >
>
>
Received on Thu May 27 1999 - 15:03:54 CDT