Pick until enough

[* Tong *]

[Note] this question is cross-posted to 3 unrelated ngs. Please followup
only to 1.

Hi,

How can I do this:
select g_name from goods order by g_priority where sum(g_value) < 500

I.e., I want to orderly pick from table goods so that the total picked
value is less than a defined value.

Certainly the above sql won't work (tested under mysql). How can I do it?
Thanks!

--
Tong (remove underscore(s) to reply)
*niX Power Tools Project: http://xpt.sourceforge.net/
- All free contribution & collection

[Ian]

* Tong * wrote:

[Note] this question is cross-posted to 3 unrelated ngs. Please followup
only to 1.

Hi,

How can I do this:
select g_name from goods order by g_priority where sum(g_value) < 500

I.e., I want to orderly pick from table goods so that the total picked
value is less than a defined value.

In DB2 using the OLAP functions,

select
a.gname
from (
select
gname,
sum(g_value) over (order by g_priority) as running_total
from
goods
) as a
where
a.running_total < 500

[Serge Rielau]

* Tong * wrote:

[Note] this question is cross-posted to 3 unrelated ngs. Please followup
only to 1.

Hi,

How can I do this:
select g_name from goods order by g_priority where sum(g_value) < 500

I.e., I want to orderly pick from table goods so that the total picked
value is less than a defined value.

Certainly the above sql won't work (tested under mysql). How can I do it?
Thanks!

select g_name from
(select sum(g_value) over(order by g_priority) as running_sum,
g_name from goods) as g where running_sum < 500

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab