TRANSACT SQL - How to manually order/index a table based upon a select/where statement

Question

Hello all, I have sql puzzler today.

I have the following sql :

  SELECT MemberId, 
         UnitId, 
         MaterialLevel, 
         MaterialText, 
         ProductPercentage, 
         ProductPriority
    FROM tblWeights
ORDER BY MemberId, MaterialLevel, MaterialText, ProductPercentage DESC

Now, currently the ProductPriority is empty. What I would like to do is update this so that the first product, per level, per materialtext with the highest product percentage with "1", the second highest percentage with "2", etc, etc.

However, when the materialtext changes, this should reset itself and and start again at "1".

Can anyone think how I can do this?

Answer 1

You can use this for the initial update:

UPDATE tblWeights w1
SET    ProductPriority = (
           SELECT COUNT(*)
           FROM   tblWeights w2
           WHERE  w2.level        = w1.level
           AND    w2.materialText = w1.materialText
           AND    w2.ProductPercentage >= w1.ProductPercentage
       )

For the automatic reset, you should probably write a trigger that fires at the statement level after DELETE, UPDATE and INSERT. You can use a modified version of this statement, and only update the rows having the affected level,materialtext combination

CREATE TRIGGER aiud_tblWeights ON tblWeights 
FOR INSERT, UPDATE, DELETE
AS
IF( UPDATE (Level) OR UPDATE (MaterialText) )
BEGIN
    UPDATE tblWeights w1
    SET    ProductPriority = (
               SELECT COUNT(*)
               FROM   tblWeights w2
               WHERE  w2.level        = w1.level
               AND    w2.materialText = w1.materialText
               AND    w2.ProductPercentage >= w1.ProductPercentage
           )
    WHERE  (w1.level, w1.materialText) IN (
        SELECT level, materialText
        FROM   inserted
        UNION ALL 
        SELECT level, materialText
        FROM   deleted
    );        
END;

Answer 2

Is there any reason you want productpriority explicitly stored in the database? Sorry if I've misunderstood your question but this sounds like it could be handled with a straight ROW_NUMBER in the output query.