Re: Help? Select latest and earliest date from a row containing three dates.

dan_at_dangray.org (Dan Gray) wrote in message news:<8c6ba53b.0306111053.6c1fbe28_at_posting.google.com>...
> Hi,
> I'm stuck. Any one got any feedback on how to select from a database
> containing...
>
> pa1date | pa2date | pa3date
> 2003-03-03 | 2003-04-03 | 2003-05-03
>
> The lowest, middle and highest dates individually on request? It's a
> stats program and while I've written a perl routine to pull all three
> dates and them parse then into order it's not very elegant and there
> must be a way to do it is SQL, I just can't find it!
>
> Regards
> Dan Gray

here's a braindead way to do it off the top of my head:

select greatest(d1, d2, d3) latest,

       least(d1, d2, d3) earliest,
       to_date(
       to_number(to_char(d1, 'J')) +
       to_number(to_char(d2, 'J')) +
       to_number(to_char(d3, 'J')) -
       to_number(to_char(greatest(d1,d2,d3), 'J')) -
       to_number(to_char(least(d1,d2,d3), 'J')),
       'J') middle

there's probably a better way still. and of course this only works with 3 columns. Received on Wed Jun 11 2003 - 21:31:14 CDT