What we do since 2 queries are necessary anyway, is to select the
NEXTVAL which gives us our 'record id' up front. Then when we do our
insert, we include that field with the value we just selected from our
sequence and we're all set. Of course if the insert fails for some
reason, then you will have a gap in the IDs, but that is not a problem
to us and the query to perform the NEXTVAL is surely less expensive than
a query on the table after the insert to get the ID.
Josh Goldberg wrote:
>
> to get the last record inserted just
> select * from row order by autoincId desc limit 1;
>
> if you have several clients doing this you might want to put your insert and
> the select inside a transaction.
>
> ----- Original Message -----
> From: "Michael Kovalcik" <makd32@yahoo.com>
> To: <pgsql-admin@postgresql.org>
> Sent: Monday, June 23, 2003 11:47 AM
> Subject: [ADMIN] How do I select the last Id in a column???
>
> > Hello,
> >
> > I'm trying to find out how I select the last id in a
> > column if I don't know anything else about the row.
> > What i am doing is inserting information into my
> > Orders table and the id is auto-incrementing. The
> > next thing i want to be able to do is grab the the id
> > of the order i just inserted. How do i do this?
> >
> > Here is the code i have so far:
> >
> > my $sql = "INSERT INTO ${Orders}
> > (locationid,shippingid,statusid,date) VALUES (?
> > ,?,?,?)";
> >
> > my $sth = $dbh->prepare($sql);
> >
> > $sth->execute(${LocationID},${ShippingID},${StatusID},${date});
> > #--*************************************
> > my $sth = $dbh->prepare("SELECT id FROM ${Orders}");
> >
> > $sth->execute();
> > $OrderID = $sth->fetchrow_arrayref;
> >
> > I know the select statement that i have will not work,
> > because it will bring me every id number in the table.
> > I just want the latest one.....
> >
> > Please HELP............
> >
> > Thank you so much,
> > Mike
> >
> > __________________________________
> > Do you Yahoo!?
> > SBC Yahoo! DSL - Now only $29.95 per month!
> > http://sbc.yahoo.com
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 2: you can get off all lists at once with the unregister command
> > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> >
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
--
Bill MacArthur
Webmaster
DHS Club