Thread: increment/rank

Hello,

I am looking for a way to create an increment value for an image
database....
using an SQL database...let's say of some bodies of water:

table water
----------------------------------------------------------------------------
-
image title        |    URL            | keywords             | rank
----------------------------------------------------------------------------
-
Peaceful Stream    |    image/stream.jpg    | water peace        | 102
Wide Stream        |    image/wide.jpg    | water peace flowing    | 101
Rushing River    |    image/river.jpg    | water rushing river    | 100
Deep Ocean        |    image/ocean.jpg    | water tide ocean    | 99
Dashing Waterfall    |    image/falls.jpg    | water fall cliff    | 98

select * from water where keywords like '%water%' and keywords like
'%peace%' order by rank;

I get a two-record list of:
----------------------------------------------------------------------------
-
Peaceful Stream    |    image/stream.jpg    | water peace        | 102
Wide Stream        |    image/wide.jpg    | water peace flowing    | 101
----------------------------------------------------------------------------
-
...and I select "Wide Stream", I want to change the rank number from 101 to
102.

etc etc etc.

any ideas?  I have searched far and wide in the documentation and I am
not seeing the answer aside from just duplicating the entire row
and incrementing the rank number, and then deleting the old record...but
that seems like a pathetic solution.

Thanks
Dan Harrington

On Tue, Dec 19, 2000 at 07:29:33PM -0700, Dan Harrington wrote:
> I get a two-record list of:
> ----------------------------------------------------------------------------
> -
> Peaceful Stream    |    image/stream.jpg    | water peace        | 102
> Wide Stream        |    image/wide.jpg    | water peace flowing    | 101
> ----------------------------------------------------------------------------
> -
> ...and I select "Wide Stream", I want to change the rank number from 101 to
> 102.

Your description of the problem is ambiguous, and so my answer which
is very simplistic may not be useful at all.  Properly set-up tables
need a primary key which is unique for each record; I'm making the
bold assumption that in your case the image filename will do as a
primary key.  So if you only want to "change the rank number" you
could do something like

update images set rank=102 where filename='image/wide.jpg'.

If the rank field has more semantics than you mentioned in your email,
you should define them for us.


--
Matt Beauregard
Information Technology Operations, DesignScape

Ph: +61 2 9361 4233   Fx: +61 2 9361 4633