Re: Inserting a select statement result into another table

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
> );