Re: SQL script question / group by ?

This might work:
select store_num, col1, col2
from states a
where trading_date =
( select next_date from states b
[Quoted]   where to_char(b.trading_date, 'YYYYMMDD') = '19991108'    and b.store_num = a.store_num);

In article <81m78g$eeg$1_at_nnrp1.deja.com>,   Antoine Theytaz <antoine.theytaz_at_gmvs.migros.ch> wrote:
> Hi all,
>
> I'm trying to get informations with SQL*Plus from one of our history
table
> named 'states'. This table has all informations about our stores
> opening/closing dates.
>
> I must find for each store its next date of opening 'next_date' given
a date
> 'trading_date' as parameter.
>
> This parameter 'trading_date' in my example is '19991108'.
>
> I'm trying to retrieve all records with one single request but I
can't see
> how.
>
> I have used 'union' but I feel this is possible with a 'group by'
clause or
> something like that...
>
> Here is my script :
>
> select col1, col2
> from states
> where store_num = 3700
> and trading_date = (select next_date from states
> where to_char(trading_date, 'YYYYMMDD') = '19991108'
> and store_num = 3700);
>
> union
>
> select col1, col2
> from states
> where store_num = 4200
> and trading_date = (select next_date from states
> where to_char(trading_date, 'YYYYMMDD') = '19991108'
> and store_num = 4200);
>
> union
>
> select col1, col2
> from states
> where store_num = 6300
> and trading_date = (select next_date from states
> where to_char(trading_date, 'YYYYMMDD') = '19991108'
> and store_num = 6300);
>
> ... and so on for all our stores.
>
> n.b: each store has its proper opening/closing dates
>
> Can you help me ? Thank you !
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Nov 26 1999 - 17:01:48 CET