Re: Inserting a select statement result into another table

He does ask a legitimate question though. If you are going to have a
LIMIT feature (which of course is not pure SQL), there seems no reason
you shouldn't be able to insert the result into a table.

Ben Adida wrote:
>
> The reason this isn't working is because there is no concept of an inherent order of rows
> in SQL. The only time things are ordered are when you explicitly request them to be,
> according to a particular field. Thus, inserting a bunch of rows is exactly the same no
> matter what order you insert them in, and you shouldn't assume anything about the
> underlying mechanism of insertion and oids in your application.
>
> What is the purpose you're trying to accomplish with this order by? No matter what, all the
> rows where done='f' will be inserted, and you will not be left with any indication of that
> order once the rows are in the todolist table.
>
> -Ben
>
> Andrew Selle wrote:
>
> > 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
> > );