sql - Filtering by window function result in Postgresql -
ok, joke had friend of mine, turned interesting technical question :)
i have following stuff
table:
create table stuff ( id serial primary key, volume integer not null default 0, priority smallint not null default 0, );
the table contains records of stuff, respective volume , priority (how need it).
i have bag specified volume, 1000
. want select table stuff can put bag, packing important stuff first.
this seems case using window functions, here query came with:
select s.*, sum(volume) on previous_rows total stuff s total < 1000 window previous_rows (order priority desc rows between unbounded preceding , current row) order priority desc
the problem it, however, postgres complains:
error: column "total" not exist line 3: total < 1000
if remove filter, total column gets calculated, results sorted all stuff gets selected, not want.
so, how do this? how select items can fit bag?
i haven't worked postgresql. however, best guess using inline view.
select a.* ( select s.*, sum(volume) on previous_rows total stuff s window previous_rows ( order priority desc rows between unbounded preceding , current row ) order priority desc ) a.total < 1000;
Comments
Post a Comment