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,2It 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 993753But 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 1Please 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.