Thread: How do I select the last Id in a column???

How do I select the last Id in a column???

From
Michael Kovalcik
Date:
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

Re: How do I select the last Id in a column???

From
"Josh Goldberg"
Date:
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)
>


Re: How do I select the last Id in a column???

From
Ericson Smith
Date:
To get the ID after the fact:
   SELECT currval('my_seq') AS id;

To get the ID before even inserting it
   SELECT nextval('my_seq') AS id;


- Ericson Smith
eric@did-it.com


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


Re: How do I select the last Id in a column???

From
"scott.marlowe"
Date:
The proper way to do this is basically this:

begin;
insert into table test values ('abc',123);
select currval('sequsedbytest');
insert into dependent_table values (keyfromabovecurrval,'moredata',456);
commit;

On Mon, 23 Jun 2003, Michael Kovalcik wrote:

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


Re: How do I select the last Id in a column???

From
DHS Webmaster
Date:
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

Re: How do I select the last Id in a column???

From
Bruno Wolff III
Date:
On Thu, Jun 26, 2003 at 14:00:33 -0700,
  Josh Goldberg <josh@4dmatrix.com> 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.

That won't help. Your suggestion will only work in serializable transactions.

In read committed mode the select might see a value for autoincId from
a transaction that committed between the insert and the select.

Re: How do I select the last Id in a column???

From
"Josh Goldberg"
Date:
From what Bruno enlightened us to about read commit mode, it seems this is
the only safe way to go.

Wouldn't currval (as presented by others) be affected in the same manner as
my example?

----- Original Message -----
From: "DHS Webmaster" <webmaster@dhs-club.com>
To: "Michael Kovalcik" <makd32@yahoo.com>
Cc: <pgsql-admin@postgresql.org>
Sent: Thursday, June 26, 2003 2:37 PM
Subject: Re: [ADMIN] How do I select the last Id in a column???


> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>


Re: How do I select the last Id in a column???

From
Bruno Wolff III
Date:
On Thu, Jun 26, 2003 at 17:46:14 -0700,
  Josh Goldberg <josh@4dmatrix.com> wrote:
> From what Bruno enlightened us to about read commit mode, it seems this is
> the only safe way to go.
>
> Wouldn't currval (as presented by others) be affected in the same manner as
> my example?

No because currval returns the last value used in the current session.

Re: How do I select the last Id in a column???

From
"Josh Goldberg"
Date:
What could cause a table to act serialized when read committed transactions
are set in the configuration?  That is something I am running into, which
provoked my [incorrect] example.

From: "Bruno Wolff III" <bruno@wolff.to>
Sent: Thursday, June 26, 2003 4:30 PM
> On Thu, Jun 26, 2003 at 14:00:33 -0700,
>   Josh Goldberg <josh@4dmatrix.com> 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.
>
> That won't help. Your suggestion will only work in serializable
transactions.
>
> In read committed mode the select might see a value for autoincId from
> a transaction that committed between the insert and the select.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>


Re: How do I select the last Id in a column???

From
Michael Kovalcik
Date:
All,

i got the answer i was needing.  I used the following:

select max(id) from table

it works great!! :)

--- Josh Goldberg <josh@4dmatrix.com> wrote:
> What could cause a table to act serialized when read
> committed transactions
> are set in the configuration?  That is something I
> am running into, which
> provoked my [incorrect] example.
>
> From: "Bruno Wolff III" <bruno@wolff.to>
> Sent: Thursday, June 26, 2003 4:30 PM
> > On Thu, Jun 26, 2003 at 14:00:33 -0700,
> >   Josh Goldberg <josh@4dmatrix.com> 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.
> >
> > That won't help. Your suggestion will only work in
> serializable
> transactions.
> >
> > In read committed mode the select might see a
> value for autoincId from
> > a transaction that committed between the insert
> and the select.
> >
> > ---------------------------(end of
> broadcast)---------------------------
> > TIP 8: explain analyze is your friend
> >
>


__________________________________
Do you Yahoo!?
SBC Yahoo! DSL - Now only $29.95 per month!
http://sbc.yahoo.com

Re: How do I select the last Id in a column???

From
"scott.marlowe"
Date:
Note that that has race conditions, i.e. two clients operating at the same
time could get the same max(id).

The setval currval and nextval functions exist to prevent race conditions,
and they work well.  Rolling your own is  a recipe for disaster.

On Fri, 27 Jun 2003, Michael Kovalcik wrote:

> All,
>
> i got the answer i was needing.  I used the following:
>
> select max(id) from table
>
> it works great!! :)
>
> --- Josh Goldberg <josh@4dmatrix.com> wrote:
> > What could cause a table to act serialized when read
> > committed transactions
> > are set in the configuration?  That is something I
> > am running into, which
> > provoked my [incorrect] example.
> >
> > From: "Bruno Wolff III" <bruno@wolff.to>
> > Sent: Thursday, June 26, 2003 4:30 PM
> > > On Thu, Jun 26, 2003 at 14:00:33 -0700,
> > >   Josh Goldberg <josh@4dmatrix.com> 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.
> > >
> > > That won't help. Your suggestion will only work in
> > serializable
> > transactions.
> > >
> > > In read committed mode the select might see a
> > value for autoincId from
> > > a transaction that committed between the insert
> > and the select.
> > >
> > > ---------------------------(end of
> > broadcast)---------------------------
> > > TIP 8: explain analyze is your friend
> > >
> >
>
>
> __________________________________
> Do you Yahoo!?
> SBC Yahoo! DSL - Now only $29.95 per month!
> http://sbc.yahoo.com
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
>                http://archives.postgresql.org
>


Re: How do I select the last Id in a column???

From
Bruno Wolff III
Date:
On Fri, Jun 27, 2003 at 10:06:41 -0700,
  Josh Goldberg <josh@4dmatrix.com> wrote:
> What could cause a table to act serialized when read committed transactions
> are set in the configuration?  That is something I am running into, which
> provoked my [incorrect] example.

Getting lucky. Did you actually run two transactions in parallel and stop
between the select and insert so that you could do a select, insert and
commit in the other transaction?

Re: How do I select the last Id in a column???

From
"Josh Goldberg"
Date:
I did, and the second transaction wouldn't even complete the insert until
the first transaction commit'd or rollback'd.  I created two new tables and
tried on there and it produced the expected behaviour, but several of my
existing tables do not.

I changed my code to use currval from now on, just to play it safe :-)

----- Original Message -----
From: "Bruno Wolff III" <bruno@wolff.to>
To: "Josh Goldberg" <josh@4dmatrix.com>
Cc: "Michael Kovalcik" <makd32@yahoo.com>; <pgsql-admin@postgresql.org>
Sent: Friday, June 27, 2003 4:48 PM
Subject: Re: [ADMIN] How do I select the last Id in a column???


> On Fri, Jun 27, 2003 at 10:06:41 -0700,
>   Josh Goldberg <josh@4dmatrix.com> wrote:
> > What could cause a table to act serialized when read committed
transactions
> > are set in the configuration?  That is something I am running into,
which
> > provoked my [incorrect] example.
>
> Getting lucky. Did you actually run two transactions in parallel and stop
> between the select and insert so that you could do a select, insert and
> commit in the other transaction?
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>