A tricky sql-query... - Mailing list pgsql-sql

We have a small association and the association has a cabin. Members of the
association can rent a term to stay in the cabin but as the cabin has turned
out to be very famous we have had to establish an application policy for
that.

It goes like this:

1. There's a seniority queue for this purpose (once you've got a term you'll
be placed in the last position in the queue)
2. Members can apply for one or more of the terms
3. The top one member in this seniority queue gets the term he applies.
4. The second member in the queue gets the term he primarly applies unless
it's not being taken by the first member. If this is the case then take his
secondary quest.
5. The third member gets the term he's primarly applied unless it's not
being taken by the first or the second applicant. If it is then try his
secondary application. If that's taken as well then try his 3rd quest (if he
has such)
6. and so on..

So, (if you didn't understand anything it's OK, pardon my poor English) if I
have a table for the applies:

CREATE TABLE apply_demo (   memberid integer,   sen integer,   priority integer,   termid integer
);


INSERT INTO apply_demo VALUES (2041, 115, 1, 15);
INSERT INTO apply_demo VALUES (2041, 115, 2, 18);
INSERT INTO apply_demo VALUES (2041, 115, 3, 19);
INSERT INTO apply_demo VALUES (206, 120, 1, 13);
INSERT INTO apply_demo VALUES (6571, 184, 1, 16);
INSERT INTO apply_demo VALUES (123340, 213, 1, 4);
INSERT INTO apply_demo VALUES (123340, 213, 2, 16);
INSERT INTO apply_demo VALUES (123340, 213, 3, 9);
INSERT INTO apply_demo VALUES (152946, 301, 1, 5);
INSERT INTO apply_demo VALUES (152880, 302, 1, 13);
INSERT INTO apply_demo VALUES (152880, 302, 2, 14);
INSERT INTO apply_demo VALUES (181333, 332, 1, 17);
INSERT INTO apply_demo VALUES (242502, 462, 1, 9);
INSERT INTO apply_demo VALUES (246024, 473, 1, 18);
INSERT INTO apply_demo VALUES (246024, 473, 2, 19);
INSERT INTO apply_demo VALUES (246024, 473, 3, 13);
INSERT INTO apply_demo VALUES (245954, 475, 1, 11);
INSERT INTO apply_demo VALUES (245954, 475, 2, 12);
INSERT INTO apply_demo VALUES (245954, 475, 3, 16);
INSERT INTO apply_demo VALUES (245954, 475, 4, 8);
INSERT INTO apply_demo VALUES (152972, 510, 1, 13);
INSERT INTO apply_demo VALUES (152972, 510, 2, 4);
INSERT INTO apply_demo VALUES (152972, 510, 3, 16);
INSERT INTO apply_demo VALUES (152972, 510, 4, 22);
INSERT INTO apply_demo VALUES (152972, 510, 5, 2);
INSERT INTO apply_demo VALUES (254085, 537, 1, 8);
INSERT INTO apply_demo VALUES (288842, 640, 1, 8);


I'd need to get out something like this:
termid | gotby
--------+--------     2 |     3 |     4 | 123340     5 | 152946     6 |     7 |     8 | 254085     9 | 242502    10 |
11 | 245954    12 |    13 |    206    14 | 152880    15 |   2041    16 |   6571    17 | 181333    18 | 246024    19 |
20 |    21 |    22 | 152972
 
(21 rows)


I know you Gurus are busy and as you are, don't spend too much time on this
because it has already been implemented with PL/PgSQL.

But just out of the curiosity - and for the educational purposes :) - I'd
like to know whether you can do this with a single sql-query?

You can't have any recursion in an pure sql-query, can you?

Regards,
Timo