Re: Select max problem

From: VC <boston103_at_hotmail.com>
Date: Tue, 09 Mar 2004 15:45:27 GMT
Message-ID: <rEl3c.160065$4o.203506@attbi_s52>

Hello,

"Marc-Antoine" <marco_at_systemmedia.com> wrote in message news:200439-121951-517067_at_foorum.com...
>
> Hello,
>
> I have this table :
>
> id1 id2 date_begin date_end id
> 1 1 10/03/2004 16/03/2004 3
> 2 1 10/03/2004 17/03/2004 3
> 3 2 10/03/2004 17/03/2004 4
> 4 2 11/03/2004 16/03/2004 4
>
> I would like to get id1 for each id and date_begin = max(date_begin)
> *but* if there are many rows returned (id = 3 in this sample), I must get
also
> max(date_end)
> (in this sample, I should get id = 3, id1 = 2 and id = 4, id1 = 4 )
>
> I've tried with group by having without success...
>
> Thanks in advance

It's relatively easy with analytics:

select * from (
  select id,

       id1,
       date_begin,
        date_end,
        max_begin,
        max_end,
        count(*) over (partition by id) cnt from
    (select id,
         id1,
        date_begin,
        date_end,
        max(date_begin) over (partition by id) max_begin,
        max(date_end)   over (partition by id) max_end
     from t1)

  where date_begin=max_begin)
where date_end= case when cnt>1 then max_end else date_end end

VC Received on Tue Mar 09 2004 - 09:45:27 CST