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
>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
More information about the thelist
mailing list