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

Judah McAuley judah at alphashop.com
Thu May 10 17:46:45 CDT 2001

At 06:01 PM 5/10/01 -0700, joshua wrote:

>3.  My personal favorite, which doesn't use two separate queries OR a
>   You do this one as
>   INSERT INTO mytable
>   (user_id, field1, field2, field3, rank)
>    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.


More information about the thelist mailing list