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

Popular posts from this blog

apache - Add omitted ? to URLs -

redirect - bbPress Forum - rewrite to wwww.mysite prohibits login -

php - How can I stop spam on my custom forum/blog? -