db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From <de...@segel.com>
Subject RE: Most efficient way to get max row id?
Date Mon, 20 Oct 2008 15:52:34 GMT
I think the question that hasn't been asked is why do you want to know the
max id of a row?

If you're using an identity column then you shouldn't have to worry about
the id of the row since the identity column will create and maintain the id
for you.

If you want to, you have another option.
You can create a id table and a couple of stored procedures to access this

The table would consist of two columns. A label column and a counter column.
Your stored procedure has two options. lastVal() and getNextVal(),
getNextVal(n). (The last one is optional or could be incrementValTo(n) )

The table will be very small. A couple of rows and the lable,counter would
be unique.

This works well if you have a distributed system and you want to guarantee
unique ids across the databases. (There's more to the design, but the basic
concept is enough to answer this question.)

You can then use the id in your table, and you can always index the column
where the id is being used.

I think there should be a pattern of this somewhere...



> -----Original Message-----
> From: Kai Ponte [mailto:kai@perfectreign.com]
> Sent: Monday, October 20, 2008 12:28 AM
> To: Derby Discussion
> Subject: Re: Most efficient way to get max row id?
> On Sunday 19 October 2008 09:15:55 pm Amir Michail wrote:
> > > Do you have an index on the identity column?  What's your datatype?
> >
> > ~60,000 rows
> >
> > So if I put an index on the identity column, max will be fast?
> It will certainly be faster than without.
> --
> kai
> www.filesite.org || www.perfectreign.com
> Clean out a corner of your mind and creativity will instantly fill it. -
> Dee
> Hock

View raw message