pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedA tricky sql-query...
4+ messages / 4 participants
[nested] [flat]
* A tricky sql-query...
@ 2003-10-22 21:58 Timo <siroco@suomi24.fi>
0 siblings, 1 reply; 4+ messages in thread
From: Timo @ 2003-10-22 21:58 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: A tricky sql-query...
@ 2003-11-03 00:42 Mark Stosberg <mark@summersault.com>
parent: Timo <siroco@suomi24.fi>
0 siblings, 2 replies; 4+ messages in thread
From: Mark Stosberg @ 2003-11-03 00:42 UTC (permalink / raw)
To: pgsql-sql
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/
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: A tricky sql-query...
@ 2003-11-03 01:35 Rod Taylor <rbt@rbt.ca>
parent: Mark Stosberg <mark@summersault.com>
1 sibling, 0 replies; 4+ messages in thread
From: Rod Taylor @ 2003-11-03 01:35 UTC (permalink / raw)
To: Mark Stosberg <mark@summersault.com>; +Cc: pgsql-sql
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.
Attachments:
signature.asc (application/pgp-signature, 186B) download
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: A tricky sql-query...
@ 2003-11-03 01:40 Christopher Browne <cbbrowne@acm.org>
parent: Mark Stosberg <mark@summersault.com>
1 sibling, 0 replies; 4+ messages in thread
From: Christopher Browne @ 2003-11-03 01:40 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2003-11-03 01:40 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-10-22 21:58 A tricky sql-query... Timo <siroco@suomi24.fi>
2003-11-03 00:42 ` Mark Stosberg <mark@summersault.com>
2003-11-03 01:35 ` Rod Taylor <rbt@rbt.ca>
2003-11-03 01:40 ` Christopher Browne <cbbrowne@acm.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox