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