Re: SQL query mental block...

"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