create table #t ( col1 int , col2 varchar( 10 ) , col3 varchar( 10 ) );
insert #t values(  1 , 'a' , 'b' );
insert #t values(  2 , 'c' , 'f' );
insert #t values(  2 , 'e' , 'd' );
select * from #t;
/*
col1        col2       col3       
----------- ---------- ---------- 
          1 a          b
          2 c          f
          2 e          d
*/
go
-- Objective:
/*
        To get only one col2 & col3 value for each value of col1.
        It doesn't matter which row in case of more than one rows
        with the same Col1 value. So this example , uses the MIN
        function to do the same.
*/
SELECT t2.Col1 ,
       RTRIM( LEFT( t2.Col23 , 10 ) ) AS Col2 , 
       RTRIM( RIGHT( t2.Col23 , 10 ) ) AS Col3
FROM ( 
    /*
        The trick is in this derived table with the CHAR conversions.
        We concatenate the values of the 2 columns col2 + col3 & get
        the minimum of those. The conversion of CHAR ensures that the
        strings are fixed length. The individual values are again
        obtained after the MIN is done.
    */
    SELECT t1.Col1 ,
           MIN( CONVERT( char( 10 ) , t1.Col2 ) +
                CONVERT( char( 10 ) , t1.Col3 )
           ) AS Col23
    FROM #t AS t1
    GROUP BY t1.Col1
) AS t2
ORDER BY t2.Col1;
-- Output of the derived table alone:
/*
Col1        Col23                
----------- -------------------- 
          1 a         b         
          2 c         f         
*/
-- Final output:
/*
Col1        Col2       Col3       
----------- ---------- ---------- 
          1 a          b
          2 c          f
*/

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