Re: Select max problem

From: VC <boston103_at_hotmail.com>
Date: Tue, 09 Mar 2004 16:23:49 GMT
Message-ID: <pcm3c.220835$jk2.760330@attbi_s53>

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...

It's not too complicated without analytics either, although a bit ugly and less performant:

• Given:

select * from t1;

1 1 3/10/2004 3/16/2004 3
2 1 3/10/2004 3/17/2004 3
3 2 3/10/2004 3/17/2004 4
4 2 3/11/2004 3/16/2004 4

• the query will be:

select p.id,
       p.id1,
       p.max_begin,
       p.max_end

from (
  select a.id,

       id1,
       max_begin,
       max_end,
       date_end

  from t1 a,
   (select id,

           max(date_begin) max_begin,
           max(date_end)   max_end

    from t1
    group by id) b
  where a.id=b.id and date_begin=max_begin   ) p,
  (select x.id, count(*) cnt from t1 x,
    (select id, max(date_begin) max_begin from t1 group by id) y    where x.id=y.id and date_begin=max_begin    group by x.id
  ) q
where p.id=q.id
  and date_end= case when cnt>1 then max_end else date_end end

• result:

ID ID1 MAX_BEGIN MAX_END
3 2 3/10/2004 3/17/2004
4 4 3/11/2004 3/17/2004

VC Received on Tue Mar 09 2004 - 10:23:49 CST