Yes, the min value of tar_num.


>Itr's not clear what 'the first ocurrence' means. If it's MIN value, try

>SELECT
>		t1.ID,
>		SUBSTRING(T2.NAME,1,20) AS cq_Status,
>		MIN(ISNULL(t6.tar_num,0)) AS tar_num,
>                COUNT(*)
>...
>GROUP BY t1.ID,	SUBSTRING(T2.NAME,1,20)
>...
>

>>Here is a challenge: I have this query I'm trying to work on SQL Server 2000 (simplified version, there are more fields and some unions, but it's better for clarifying the problem):
>>

>>SELECT
>>		t1.ID,
>>		SUBSTRING(T2.NAME,1,20) AS cq_Status,
>>		isnull(t6.tar_num,0) as tar_num
>>	FROM CQ_USER.CQ_ADMIN.enttable T1 (NOLOCK)
>>		 INNER JOIN CQ_USER.CQ_ADMIN.statedef T2 (NOLOCK)
>>					ON T1.state = T2.id
>>		  left outer join CQ_USER.CQ_ADMIN.parent_child_links T3 (NOLOCK)
>>			on t1.dbid = t3.parent_dbid
>>			 and 16790990 = T3.parent_fielddef_id
>>		  left outer join CQ_USER.CQ_ADMIN.customercase T4 (NOLOCK)
>>			on t3.child_dbid = t4.dbid
>>		  left outer join ccmast.dbo.nxstar_e T6 (NOLOCK)
>>			on t4.case_num = t6.tar_num	
>>	where  T1.dbid <> 0
>>		and  t1.Product in ('CampusVue','Portal')
>>		and SUBSTRING(T2.NAME,1,20) in
>>			('Design', 'Evaluate','Development',
>'Escalate', 'New', 'Re-design', 'Reevaluate',
>'Requirements', 'Test', 'Waiting')
>>order by 1,2
>>

>>
>>It produces a number of rows like this
>>

>>ID              cq_Status       tar_num
>>CMC_U00057767	New	        992799
>>CMC_U00057768	Evaluate	993825
>>CMC_U00057768	Evaluate	993885
>>CMC_U00057768	Evaluate	993905
>>CMC_U00057768	Evaluate	993762
>>CMC_U00057768	Evaluate	993906
>>CMC_U00057768	Evaluate	993712
>>CMC_U00057771	Evaluate	993722
>>CMC_U00057776	New	        993761
>>CMC_U00057776	New	        993796
>>CMC_U00057776	New	        993786
>>CMC_U00057776	New	        993792
>>CMC_U00057776	New	        993747
>>CMC_U00057776	New	        993830
>>CMC_U00057776	New	        993764
>>CMC_U00057776	New	        993764
>>CMC_U00057776	New	        993752
>>CMC_U00057777	New	        993753
>>

>>
>>But what I really need is the first ocurrence of each tar_num per ID and a count:
>>

>>ID              cq_Status       tar_num  cases count
>>CMC_U00057767	New	        992799   1
>>CMC_U00057768	Evaluate	993825   7
>>CMC_U00057771	Evaluate	993722   1
>>CMC_U00057776	New	        993761   9
>>CMC_U00057777	New	        993753   1
>>

>>
>>Please help me, I'm blocked and can't find a solution, I tried using groupings, min(), having, etc, but can't do it. The count can be ommited if it causes much trouble.
>>
>>Many thanks in advance,
>>
>>Max