Trouble with a SELECT with DISTINCT requirement

[tbode2006]

Given the following sample table:
col_A col_B col_C
123 RED 4
234 BLUE 2
123 RED 7
345 GREEN 1
123 BLUE 5
456 RED 6
234 BLUE 3

I would like to generate the code to return:
col_A col_B col_C col_D
345 GREEN 1 1
234 BLUE 2 2
123 RED 2 4
123 BLUE 1 5
456 RED 1 6

where col_C is the count of DISTINCT(col_A+col_B) and col_D is ordered on the value of the first occurrance. I am having trouble with the DISTINCT requirement as well as the first VALUE.

 

[PerlyGates]

Is this what you want?

DECLARE @tv1 TABLE (
	col_A   varchar(10),
	col_B   varchar(10),
	col_C   int
)


INSERT INTO @tv1 
SELECT '123'     ,'RED',     4 UNION ALL
SELECT '234'     ,'BLUE',    2 UNION ALL
SELECT '123'     ,'RED',     7 UNION ALL
SELECT '345'     ,'GREEN',   1 UNION ALL
SELECT '123'     ,'BLUE',    5 UNION ALL
SELECT '456'     ,'RED',     6 UNION ALL
SELECT '234'     ,'BLUE',    3 

SELECT t1.col_A
      ,t1.col_B
      ,t2.myCount AS col_C
      ,t2.col_C  AS col_D
  FROM @tv1  t1
 INNER 
  JOIN (SELECT COUNT(*) AS myCount 
              ,col_A
              ,col_B
              ,MIN(col_C) AS col_C 
         FROM @tv1 
        GROUP
           BY col_A
             ,col_B ) t2
   ON t1.col_A       = t2.col_A
  AND t2.col_B       = t2.col_B
  AND t1.col_C       = t2.col_C
ORDER
   BY t2.col_C

 

[r937]

select foo.col_A   
     , foo.col_B 
     , bar.countC  as col_C
     , foo.col_C   as col_D
  from daTable as foo
inner
  join (
       select col_A
            , col_B
            , count(*) as countC
            , min(col_C) as minC
         from daTable
       group
           by col_A
            , col_B
       ) as bar
    on bar.col_A = foo.col_A
   and bar.col_B = foo.col_B
   and bar.minC  = foo.col_C

r937.com | rudy.ca

 

[r937]

aaarghh, sniped again!

r937.com | rudy.ca

 

[SQLSister]

Not too bad if you use a temp table with an idfield in order to grab the first value. See if this does waht you want

create table #temp (cola int, colb varchar (10), colc int, idfield int identity (1,1))
insert into #temp
select 123,     'RED',     4
union all
select 234,     'BLUE',    2
union all
select 123 ,    'RED',     7
union all
select 345 ,    'GREEN',   1
union all
select 123 ,    'BLUE',    5
union all
select 456,     'RED',     6
union all
select 234,     'BLUE',    3

select cola, colb, counted as colc, colc as cold from #temp t
join
(select  count(colc)as counted, min(idfield) as idfield
from #temp group by cast(cola as varchar(10))+colb) a
on a.idfield = t.idfield

"NOTHING is more important in a database than integrity." ESquared

 

[tbode2006]

Thanks all for the quick replies! Let me try these out on actual data sets and I will let you know.