Thread

A tricky sql-query...

Timo <siroco@suomi24.fi> — 2003-10-22T21:58:08Z

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

Re: A tricky sql-query...

Mark Stosberg <mark@summersault.com> — 2003-11-03T00:42:46Z

On 2003-10-22, Timo <siroco@suomi24.fi> wrote:
>
> You can't have any recursion in an pure sql-query, can you?

It depends on how you think of recursion, I'd say. You join on the same
table a number of times, by giving it a different alias each time. You 
have to manually specify (or generate with application code) all these 
aliases and joins, though. Sometimes people use this technique to
implement tree structures in SQL.  

	Mark

-- 
http://mark.stosberg.com/

Re: A tricky sql-query...

Rod Taylor <rbt@rbt.ca> — 2003-11-03T01:35:13Z

On Sun, 2003-11-02 at 19:42, Mark Stosberg wrote:
> On 2003-10-22, Timo <siroco@suomi24.fi> wrote:
> >
> > You can't have any recursion in an pure sql-query, can you?
> 
> It depends on how you think of recursion, I'd say. You join on the same
> table a number of times, by giving it a different alias each time. You 
> have to manually specify (or generate with application code) all these 
> aliases and joins, though. Sometimes people use this technique to
> implement tree structures in SQL.  

Not to mention the WITH .. RECURSIVE clause, not yet in PostgreSQL.

Re: A tricky sql-query...

Chris Browne <cbbrowne@acm.org> — 2003-11-03T01:40:15Z

Quoth mark@summersault.com (Mark Stosberg):
> On 2003-10-22, Timo <siroco@suomi24.fi> wrote:
>>
>> You can't have any recursion in an pure sql-query, can you?
>
> It depends on how you think of recursion, I'd say. You join on the same
> table a number of times, by giving it a different alias each time. You 
> have to manually specify (or generate with application code) all these 
> aliases and joins, though. Sometimes people use this technique to
> implement tree structures in SQL.  

There is apparently an SQL 1999 feature that expressly supports
recursion.

WITH RECURSIVE 
    Q1 AS SELECT ... FROM ... WHERE ...
    Q2 AS SELECT ... FROM ... WHERE ...
  SELECT ... FROM Q1, Q2 WHERE ...

See example here...

<http://www.dbaiien.ac.at/proj/dlv/sql/.tuw>

There is presumably some work ongoing; support for WITH RECURSIVE is
on the TODO list, and has been discussed before...

http://archives.postgresql.org/pgsql-hackers/2003-05/msg00657.php
-- 
output = reverse("gro.mca" "@" "enworbbc")
http://www.ntlug.org/~cbbrowne/linuxdistributions.html
"Listen,  strange women, lyin'  in ponds,  distributin' swords,  is no
basis  for a  system of  government. Supreme  executive  power derives
itself from a mandate from  the masses, not from some farcical aquatic
ceremony."  -- Monty Python and the Holy Grail