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
applicationlevel I think
3. use a temporary table with a SELECT INTO statement and then copy the contents of the temporary table into the table
Iwant 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
);