strangely familiar Posted September 22, 2006 Share Posted September 22, 2006 Hi :)I'm new to SQL and the forum, and hoping someone can help. I'm trying to pull data for a report...not sure if I'm on the right track or not...For all records found with the selected criteria, I need the top TaskID...here's what I have so far...QUERY:SELECT WRP.CustID, CMT.TaskID, CMT.ServiceNumber FROM dbo.ACN_CCPWrapCode WRP, dbo.ACN_CCPCaseManagementTask CMT WHERE WRP.CustID=CMT.CustID AND WRP.CallTypeID = '6' AND WRP.CallCategoryID = '223'AND WRP.CallCategoryReasonID = '427' AND WRP.DateCreated between '2006-09-12 00:00:00.000' and '2006-09-12 23:59:26.599'ORDER BY WRP.CustID, CMT.TaskID DESCRESULTS:CustID TaskID ServiceNumber47267 5388607 650728532447267 5326788 650728532447267 5136222 650728532447267 4946900 650728532460947 5388944 716823147660947 5345550 716823147660947 5319921 716823147660947 5319901 716823147660947 5294399 716823147660947 5249701 716823147660947 188072 716823147664983 5361067 716691600489501 5393686 708449562489501 5390907 708449562489501 5388819 708449562489501 5359178 7084495624I want the results to contain the first row for each CustID, with the newest TaskID (greatest number), so I would like the result to look like this:47267 5388607 650728532460947 5388944 716823147664983 5361067 716691600489501 5393686 7084495624Does anyone know how to do this?thanks Link to comment Share on other sites More sharing options...
aspnetguy Posted September 22, 2006 Share Posted September 22, 2006 change ORDER BY WRP.CustID, CMT.TaskID DESC toORDER BY CMT.TaskID DESC Link to comment Share on other sites More sharing options...
strangely familiar Posted September 22, 2006 Author Share Posted September 22, 2006 change ORDER BY WRP.CustID, CMT.TaskID DESC toORDER BY CMT.TaskID DESCSorry, I'm probably not asking the question properly. I changed what you said, but there are still multiple records with the same CustID. I need only one record for each CustID, along with the TaskID that is the largest number, and the ServiceNumber. Changing the sort does not remove the extra records from the result... Link to comment Share on other sites More sharing options...
aspnetguy Posted September 22, 2006 Share Posted September 22, 2006 I see what you mean. You are going to need to look into GROUP BY and MAX() Link to comment Share on other sites More sharing options...
lordfa9 Posted November 3, 2006 Share Posted November 3, 2006 I see what you mean. You are going to need to look into GROUP BY and MAX()i think what he means is to use MAX(taskid) for the select statement and to use a 'GROUP BY CustID'i'm not sure about the group by thing though, i think you have to use DISTINCT(CustID) in your select statement Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now