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

[Judah McAuley]

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