[thelist] CF: Dynamic variables and Insert Statements

Joshua OIson joshua at alphashop.net
Thu May 10 17:00:29 CDT 2001


Rudy,

Thank you for the clarification to some of my comments.  I tend to glaze
over concepts sometimes!

-joshua

<tip type="SQL - a ranking column">
There are a lot of times when a rank column is important in a database
architecture.
For example, you may have a resume application that stores the "experience"
items
a person has in there resume.  The application may be using rank to order
them on
the final output instead of dates because of the arbitrary overlapping of
jobs can
lead to total chaos.

When you insert a new item, it's common to put it at the bottom of the list
by setting its
rank to Max(rank) + 1.  You can do this in a couple of different ways.

1. Query the db to find the max rank, then do an insert, using the answer
from the first query
    to drive the results of the insert.  This method uses two queries, a
SELECT and an UPDATE,
   and assumes you have the ability to single thread the databse engine.

2. You can set up a trigger to update the rank automatically:

  CREATE TRIGGER update_rank ON mytable
   FOR INSERT AS
      UPDATE mytable
        SET mytable.rank = (SELECT COALESCE(MAX(rank),0) FROM mytable)+1
      FROM inserted
      WHERE mytable.id = mytable.id

  Assuming that id is a primary key.  If you need some conditionals for
ranking, such as
  you may want to keep a running rank for each user in the system based on
user id,
  put the conditional in the WHERE clause within the COALESCE.

  (note, the COALESCE is important because NULL+1 = NULL!  If this is the
first record
   to be inserted, you'll need the COALESCE to make sure it gets a ranking
of 1)

  This method uses three queries, the orginal insert, the update, and a
subselect.

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

  Again, the conditional clause is whatever conditionals you need to specify
to make the
  rank only depend on other records within the same "business grouping".  In
this case, that
  would be only the experience entries for the person who's making the
resume.

  This method uses two queries, one select and one insert, and does not
assume  that you
  can single thread the database engine.
</tip>





More information about the thelist mailing list