Assume I have a budget of $10 (any integer) and I want to distribute it over records which have rank field with varying needs. Example:

rank     Req.    Fulfilled?  
1       $3           Y  
2       $4           Y  
3       $2           Y  
4       $3           N  

Those ranks from 1 to 3 should be fulfilled because they are within budget. whereas, the one ranked 4 should not.

I want an SQL query to solve that.

Below is my initial script:

CREATE TABLE budget (
id VARCHAR (32),
budget INTEGER,
PRIMARY KEY (id));

CREATE TABLE component (
id VARCHAR (32),
rank INTEGER,
req INTEGER,
satisfied BOOLEAN,
PRIMARY KEY (id));

INSERT INTO budget (id,budget) VALUES ('1',10);
INSERT INTO component (id,rank,req) VALUES ('1',1,3);
INSERT INTO component (id,rank,req) VALUES ('2',2,4);
INSERT INTO component (id,rank,req) VALUES ('3',3,2);
INSERT INTO component (id,rank,req) VALUES ('4',4,3);

Thanks in advance for your help.

Lee