CREATE TABLE #SeqTbl (
        Seq int NOT NULL DEFAULT ( 0 ) ,
        C1 varchar( 10 ) NULL ,
        C2 varchar( 20 ) NULL ,
        UNIQUE ( C1 , C2 )
);
-- Sample data
INSERT INTO #SeqTbl ( C1 , C2 )
SELECT 'a' AS c1 , 'b' AS c2
UNION ALL
SELECT 'x' , 'y'
UNION ALL
SELECT '1' , NULL
UNION ALL
SELECT '*' AS c1 , '$' AS c2
UNION ALL
SELECT NULL , 'y'
UNION ALL
SELECT NULL , NULL;

SELECT * FROM #SeqTbl
/*
Seq         C1         C2                   
----------- ---------- -------------------- 
          0 a          b
          0 x          y
          0 1          NULL
          0 *          $
          0 NULL       y
          0 NULL       NULL
*/
GO
/*
        Objective: To sequence the rows in the order of C1 , C2. The
                   sequence numbers should match say a query with ORDER BY
                   clause of C1 , C2 viz: "SELECT * FROM tbl ORDER BY C1, C2".
        Solution : Concatenate the two columns & count the values 
                   less than or equal to a particular value.
                   The values should always be converted to fixed-length for
                   this logic to work. The CHAR(0) value for NULLs is used to
                   sort the NULL values on top. You can use a similar technique
                   to sort the NULL values at the bottom.
*/
-- Query that shows the concatenation results that will be used:
SELECT COALESCE( CONVERT( char( 10 ) , t1.C1 ) , REPLICATE( CHAR( 0 ) , 10 ) ) +
       COALESCE( CONVERT( char( 20 ) , t1.C2 ) , REPLICATE( CHAR( 0 ) , 20 ) ) AS OrderCols
FROM #SeqTbl AS t1
/*
OrderCols                      
------------------------------ 


*         $                   
1         
a         b                   
x         y                   
*/
UPDATE #SeqTbl
SET Seq =  ( SELECT COUNT( * ) FROM #SeqTbl AS t2
             WHERE COALESCE( CONVERT( char( 10 ) , t2.C1 ) , REPLICATE( CHAR( 0 ) , 10 ) ) +
                   COALESCE( CONVERT( char( 20 ) , t2.C2 ) , REPLICATE( CHAR( 0 ) , 20 ) )
                   <=
                   COALESCE( CONVERT( char( 10 ) , #SeqTbl.C1 ) , REPLICATE( CHAR( 0 ) , 10 ) ) +
                   COALESCE( CONVERT( char( 20 ) , #SeqTbl.C2 ) , REPLICATE( CHAR( 0 ) , 20 ) )
);
SELECT * FROM #SeqTbl
ORDER BY Seq;
/*
Seq         C1         C2                   
----------- ---------- -------------------- 
          1 NULL       NULL
          2 NULL       y
          3 *          $
          4 1          NULL
          5 a          b
          6 x          y
*/
GO
DROP TABLE #SeqTbl;

This page was last updated on May 01, 2006 04:28 PM.