Re: SQL query mental block...my solution

From: Glen A Stromquist <glen_stromquist_at_no_spam_yahoo.com>
Date: Wed, 28 Jul 2004 21:36:47 GMT
Message-ID: <P%UNc.114368$Rf.73989@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. Received on Wed Jul 28 2004 - 16:36:47 CDT