On 12/1/06,
Markus Juenemann <
markus@juenemann.net> 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==================================================================