On 12/1/06, Markus Juenemann <[EMAIL PROTECTED]> wrote:
Hi (again!) [stupid email program sent my message before I finished it!!!] I've got a bit of a tricky (for me!) problem. The example below is completely ficticious but describes my real problem in a way which might be easier to understand. Imagine the table contains a list of passenger wanting to get on a small(!) plane. The plane can carry at most 200kg of passengers and will be filled strictly on a first-come first-serve basis - well, check-in staff is a bit stupid ;-). So what needs to be done is to set the 'gets_seat' column to true until the weight limit is reached. CREATE TABLE passenger_queue ( id serial NOT NULL, name character varying(40) NOT NULL, weight integer NOT NULL, gets_seat boolean default false ) insert into passenger_queue values (1,"Peter",75,false) insert into passenger_queue values (2,"Mary",50,false) insert into passenger_queue values (3,"John",70,false) insert into passenger_queue values (4,"Steve",80,false) According to the specifications given above Peter, Mary and John would have 'gets_seat' set to true because their cumulative weight is 195kg while Steve misses out. The big question is: How can I do this in a nice SQL query???
I would ditch the gets_seat column and instead create a view that calculates the value when you need it. This helps eliminate redundant data. CREATE VIEW passenger_queue_vw ( id, name, weight, gets_seat ) AS SELECT queue.id, queue.name, queue.weight, CASE sum(others.gets_seat) <= 200 FROM passenger_queue queue INNER JOIN passenger_queue others ON ( others.id <= queue.id -- There should really be a create date used here -- but for example purposes I assume the id column -- is an increasing sequence ) GROUP BY queue.id, queue.name, queue.weight If you have performance concerns you can create a materialized view. Of course if you don't want the record to even be allowed (cause an error on insert), you should use a constraint as mentioned in one of the other responses to your question. -Aaron -- ================================================================== Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com http://codeelixir.com ==================================================================
