Hi all,
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