From: "Timo" <siroco@suomi24.fi>
Subject: A tricky sql-query...
Date: Thu, 23 Oct 2003 00:58:08 +0300
Organization: Hub.Org Networking Services
Lines: 101
Message-ID: <bn6uhi$jp3$1@news.hub.org>
To: pgsql-sql@postgresql.org


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