Re: SQL query mental block...my solution

Glen A Stromquist <glen_stromquist_at_no_spam_yahoo.com> wrote in message news:<P%UNc.114368$Rf.73989_at_edtnps84>...
> Michel Cadot wrote:
>
> > "Glen A Stromquist" <glen_stromquist_at_no_spam_yahoo.com> a écrit dans le message de
> > news:ddyNc.112177$Rf.52794_at_edtnps84...
> >
> >>I know I have done this before, but danged if I can remember how....
> >>
> >>table is 'some_text(char) date_in(date) more_text(varchar)'
> >>
> >>column some_text is non unique and col more_text is unique
> >>
> >>I need some_text, first date_in, last date_in for each grouping of
> >>some_text - no problem:
> >>
> >>select some_text, min(date_in) first,max(date_in) last from table group
> >>by some_text.
> >>
> >>Now the users want the more_text value associated with the first date_in
> >>column and also the one associated with the last date_in column so that
> >>it looks like:
> >>
> >>some_text first more_text last more_text
> >>JA234567111 23-jul-92 H546231 28-jul-92 H532267
> >>HJ897568901 01-jun-93 K876543 12-sep-93 T876549
> >>
> >>I think I left the query I used before on our (retired) AIX box, and I'm
> >>momentarily stuck here, can someone here point out the obvious?
> >>
> >
> >
> > Have a look at:
> >
> > first/last_value(more_text) over(partition by some_text order by date_in)
> >
> > --
> > Regards
> > Michel Cadot
> >
>
>
>
> Thanks - had a good boo at the first/last_value function, one thing I
> did notice was that the order by clause seemed to be able to change the
> results to what I wanted regardless of whether I changed the first/last
> in the x_value function.
>
> In the end I could still not get it to do what I wanted, however in my
> searches I found that row_number () (partion on ___ order by ____) was
> used with x_value for similar results. After much experimentation I
> could get what I needed, and found that I could drop the x_value
> altogether and simple use row_number with inline views.
>
> Here is my solution:
> ==================================================================
> select a.tid,a.firstload,a.tm9,b.lastload,b.tm9
> from
> (
> select
> tid,firstload,tm9
> from
> (
> select
> timber_id tid
> ,tm9_number tm9
> ,datetime_in firstload
> ,row_number () over (partition by timber_id order by datetime_in asc) rown
> from tm9_form)
> where rown = 1) a,
> (select
> tid,lastload,tm9
> from
> (
> select
> timber_id tid
> ,tm9_number tm9
> ,datetime_in lastload
> ,row_number () over (partition by timber_id order by datetime_in desc) rown
> from tm9_form)
> where rown = 1) b
> where a.tid = b.tid
> and a.tid like 'F%'
> order by 2;
> ===================================================================
>
> Although this query ran pretty fast, if someone has an easier/better way
> to do this, please share it here.

It looks simplier to me:

CREATE TABLE t(some_text CHAR(20), date_in DATE, more_text VARCHAR(20))

INSERT INTO t VALUES('aaa', TO_DATE(20040101, 'YYYYMMDD'), 'bb1');
INSERT INTO t VALUES('aaa', TO_DATE(20040102, 'YYYYMMDD'), 'bb2');
INSERT INTO t VALUES('aab', TO_DATE(20040103, 'YYYYMMDD'), 'bb3');
INSERT INTO t VALUES('aab', TO_DATE(20040104, 'YYYYMMDD'), 'bb4');
INSERT INTO t VALUES('aba', TO_DATE(20040105, 'YYYYMMDD'), 'bb5');
INSERT INTO t VALUES('aba', TO_DATE(20040106, 'YYYYMMDD'), 'bb6');
INSERT INTO t VALUES('aba', TO_DATE(20040107, 'YYYYMMDD'), 'bb7');

SELECT q.some_text, q.FIRST, q.LAST, t1.more_text more_text_last, t2.more_text more_text_first
FROM ( SELECT some_text, MIN(date_in) FIRST,MAX(date_in) LAST FROM t GROUP BY some_text ) q, t t1, t t2

WHERE q.LAST = t1.date_in
AND   q.some_text = t1.some_text
AND   q.FIRST = t2.date_in
AND   q.some_text = t2.some_text

bye
Cristian Vanti Received on Fri Jul 30 2004 - 08:18:53 CDT