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