Rank Tip was: [thelist] CF: Dynamic variables and Insert Statements

[Joshua OIson]

Judah,

That's a good point.  Indexes are *usually* helpful in avoiding table scans,
so it's a good idea to look to them when optimizing.  There's a whole field
of study on that issue, so I won't go into depth, but thank you for the
reminder.

Rudy reminded me off-list that some databases don't allow you to insert into
the same table from which you select.  So, this may be the case if you are
trying to do it this way and cannot seem to get it to work.  I am curious as
to which db's fall under this restriction, if anybody is so inclined to find
out.

-joshua

----- Original Message -----
From: "Judah McAuley" <judah at alphashop.com>
Subject: Re: Rank Tip was: [thelist] CF: Dynamic variables and Insert
Statements


> At 06:01 PM 5/10/01 -0700, joshua wrote:
>
> >3.  My personal favorite, which doesn't use two separate queries OR a
> >trigger.
> >
> >   You do this one as
> >
> >   INSERT INTO mytable
> >   (user_id, field1, field2, field3, rank)
> >   SELECT
> >    1, 'constant1', 'constant2', 'constant3',
> >    COALESCE(Max(rank), 0) + 1
> >   FROM mytable
> >   WHERE user_id = 1
>
> And if I understand correctly (a point sometimes in dispute), if you
create
> an joint index on the column 'rank' and the column 'user_id', then the
> Max(rank) can be done without a table scan to find the maximum rank for a
> given user.
>
> Judah