Re: Sort Order in Nested query
- From: Stephane Faroult <sfaroult@xxxxxxxxxxxx>
- To: ftaheny@xxxxxxxxx
- Date: Fri, 01 Feb 2013 17:30:13 +0100
On 02/01/2013 12:43 PM, Fergal Taheny wrote:
> SQL*Plus <sys@ACBSOLT8> select * from transaction_stage
> 2 ;
>
> CUSTOMER_ID VALUE
> ----------- ----------
> 1 5
> 1 6
> 2 2
> 2 6
> 2 3
Fergal,
I all depends on how you want to identify your batches. Personally,
I think I would opt for a composite id such as (timestamp, customer_id,
batch_id), plus possibly a sys_guid if you can have concurrent
processes, and wouldn't use a sequence; and under this hypothesis, you
can assign (not optimally) your values to one batch with something
relatively simple, e.g.
select customer_id,
value,
ceil(sum(value)
over (partition by customer_id
order by value desc)/10) batch_id
from transaction_stage
/
Naturally, several runs will return the same batch_id values for the
same customer_id, hence my composite key with a timestamp. On the
positive side, the composite key would allow you some flexibility for
partitioning if this is ever needed.
--
Stephane Faroult
RoughSea Ltd <http://www.roughsea.com>
Konagora <http://www.konagora.com>
RoughSea Channel on Youtube <http://www.youtube.com/user/roughsealtd>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
