select distinct a.node_name, -
       a.filespace_name, -
       a.class_name, -
       a.archive_date as "Newest"-
  from archives a -
 where a.archive_date = ( -
       select max(b.archive_date) -
         from archives b -
       where a.node_name = b.node_name -
         and a.filespace_name = b.filespace_name -
                        )

select distinct a.node_name, -
       a.filespace_name, -
       a.class_name, -
       a.archive_date  as "Oldest" -
  from archives a -
 where a.archive_date = ( -
       select max(b.archive_date) -
         from archives b -
       where a.node_name = b.node_name -
         and a.filespace_name = b.filespace_name -
                        )


On Wed, 8 May 2002, Jolliff, Dale wrote:

>Not quite it yet either....
>That will result in the one max/min dated record from all filespaces, not
>the min.max for each space.
>
>
>
>
>-----Original Message-----
>From: Alex Paschal [mailto:AlexPaschal AT FREIGHTLINER DOT COM]
>Sent: Wednesday, May 08, 2002 1:00 PM
>To: ADSM-L AT VM.MARIST DOT EDU
>Subject: Re: SQL question on date manipulation
>
>
>These should give you the information you're looking for.
>
>select filespace_name, min(archive_date) from archives where
>node_name='NODE' group by filespace_name
>
>and
>
>select filespace_name, max(archive_date) from archives where
>node_name='NODE' group by filespace_name
>
>Alex Paschal
>Storage Administrator
>Freightliner, LLC
>(503) 745-6850 phone/vmail
>
>-----Original Message-----
>From: Jolliff, Dale [mailto:xjolliff AT TI DOT COM]
>Sent: Wednesday, May 08, 2002 11:30 AM
>To: ADSM-L AT VM.MARIST DOT EDU
>Subject: SQL question on date manipulation
>
>
>OK, here's a question for you SQL super-guru types...
>
>I want to extract the oldest and newest dates from the archives table for a
>particular node...
>
>What I'm looking for is a distinct record for every filespace in every
>domain with the oldest (and newest, in a separate query) archive_date
>matching a particular node ...
>
>
>select filespace_name, archive_date, class_name from archives where
>node_name='some_node'
>
>How do I get the oldest and newest dates calculated for archive_date?
>or calculate the min to max range with the result in a date display format?
>
>