Re: Inserting a select statement result into another table

Alright. My situation is this. I have a list of things that need to be done
in a table called tasks. I have a list of users who will complete these tasks.
I want these users to be able to come in and "claim" the top 2 most recent tasks
that have been added. These tasks then get stored in a table called todolist
which stores who claimed the task, the taskid, and when the task was claimed.
For each time someone wants to claim some number of tasks, I want to do something
like

INSERT INTO todolist
SELECT taskid,'1',now()
FROM tasks
WHERE done='f'
ORDER BY submit DESC
LIMIT 2;

Unfortunately, when I do this I get
ERROR: ORDER BY is not allowed in INSERT/SELECT

The select works fine

aselle=> select taskid,'1',now() FROM tasks WHERE done='f' ORDER BY submit DESC LIMIT 2;
taskid | ?column? | now
--------+----------+------------------------
4 | 1 | 2000-08-17 12:56:00-05
3 | 1 | 2000-08-17 12:56:00-05
(2 rows)

It seems to me, this is something I should do. I was wondering if there
is any reason why I can't do this? I've thought of a couple of workarounds
but they don't seem to be very clean:

1. Read the results of the select at the application level and reinsert into the
todolist table

2. Add two fields to the task table that keep track of userid and claimed.
This unfortunately clutters the main task table, and it loses the ability
to assign multiple people to the same task. It also requires looping at the
application level I think

3. use a temporary table with a SELECT INTO statement and then copy the contents
of the temporary table into the table I want it in todolist

Below are the table creation statements for this sample...

-Andy

CREATE TABLE tasks (
taskid int4,
title varchar(64),
descr text,
submit datetime,
done boolean
);

CREATE TABLE users (
userid int4,
name varchar(32)
);

CREATE TABLE todolist (
taskid int4,
userid int4,
claimed datetime
);