February 26, 2008 at 3:03 pm
Hello,
The following query:
SELECT submitRep1 AS Rep, tc, COUNT(tc)AS TCCount
FROM tbl_CYProcessedSales
WHERE
tc NOT LIKE 'T%'
AND
tc NOT LIKE 'R%'
AND ISNUMERIC(TC) = 0
AND NOT submitrep1 = ''
AND Submitrep1 = 'along'
GROUP BY submitrep1, tc
Returns a result like this:
ALONG KL65
ALONG KP35
How can I return the one record that contains the MAX(TCCount)?
Thank you for your help!
CSDunn
February 26, 2008 at 3:44 pm
How about adding a TOP (1) clause to the SELECT and an ORDER BY COUNT (tc) DESC ?
Todd Carrier
MCITP - Database Administrator (SQL 2008)
MCSE: Data Platform (SQL 2012)
February 26, 2008 at 3:47 pm
Another way to skin the cat is a HAVING clause (basically filtering your GROUP BY)...
HAVING Count(tc) = (subquery)
Todd Carrier
MCITP - Database Administrator (SQL 2008)
MCSE: Data Platform (SQL 2012)
February 26, 2008 at 3:53 pm
Todd Carrier (2/26/2008)
How about adding a TOP (1) clause to the SELECT and an ORDER BY COUNT (tc) DESC ?
If I do it like this, I just get one record back (there should be over 160 records returned). There are other records in the results, I just pulled a sample.
February 26, 2008 at 4:02 pm
The following is a larger sampling of the results:
ABEARDENWF99
ACHEATHMKS53
AHERROD WB36
ALONG KL65
ALONG KP35
AMGRANT MB4
AMILLER KW28
AMOYERS WB97
APETRSKYKD169
ASCHIMELHV8
ASCHIMELXN1
I need the distinct record that contains the MAX(TCCount). So the results should look like this:
ABEARDENWF99
ACHEATHMKS53
AHERROD WB36
ALONG KL65
AMGRANT MB4
AMILLER KW28
AMOYERS WB97
APETRSKYKD169
ASCHIMELHV8
February 26, 2008 at 4:02 pm
The easiest way is to order by cout desc and grab the first record, as Todd mentioned. I would stick with this method.
February 26, 2008 at 4:05 pm
Just saw your post. This is going to require a little more thought than an order by. Let me look at it more.
February 26, 2008 at 4:45 pm
Adam Haines (2/26/2008)
The easiest way is to order by cout desc and grab the first record, as Todd mentioned. I would stick with this method.
I tried it like this:
SELECT top 1 submitRep1 AS Rep, tc, COUNT(tc)AS TCCount
FROM tbl_CYProcessedSales
WHERE
tc NOT LIKE 'T%'
AND
tc NOT LIKE 'R%'
AND ISNUMERIC(TC) = 0
AND NOT submitrep1 = ''
GROUP BY submitrep1, tc
ORDER BY COUNT(TC) DESC
I only get one record back.
February 26, 2008 at 5:14 pm
It is much easier to help if you post code to create and populate a temp table with sample data, and what you are looking for logically from the resultset.
I think what you are going to need, based on the conversation, is a correlated subquery. As stated before, the HAVING CLAUSE is basically a WHERE statement for the GROUP by. I think you just need to add a subquery GROUP BY: something like this...
SELECT submitRep1,td,count (tc)
FROM tbl_cyprocessedsales T1
WHERE ...
GROUP BY submitRep1,tc
HAVING count (tc) = (SELECT max count(tc) from tbl_cyprocessdsales T2 where T2.submitRep1 = T1.submitRep1 and T2.td = T1.td)
Todd Carrier
MCITP - Database Administrator (SQL 2008)
MCSE: Data Platform (SQL 2012)
February 26, 2008 at 5:40 pm
cdun,
Todd's solution will do the trick.
Providing table ddl and sample data goes a long way in receiving a quality solution. Todd had to take extra steps to figure out what you were exactly after. These type of steps can be avoided by providing all the information that is pertinent to the solution. Take some time and read through this article. It will show how to post your problem properly , so it makes things easier on those trying to help you and decreases the time needed for you to receive a working solution.
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Thanks,
Adam
February 26, 2008 at 10:18 pm
Actually - I've been finding that using ROW_NUMBER() is substantially less effort than than the aggregate function method. Not quite sure why - but it seems to do better.
;with GroupedCTE as (
Select Row_number over (partition by submitrep1, tc Order by submitrep1, tc, tccount DESC) RN,
*
from (select submitrep1,tc,count(*) tccount
from tbl_CYProcessedSales
where
tc NOT LIKE 'T%'
AND tc NOT LIKE 'R%'
AND ISNUMERIC(TC) = 0
AND NOT submitrep1 = ''
AND Submitrep1 = 'along'
GROUP BY submitrep1, tc) tcc
)
Select * from GroupedCTE where RN=1
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 27, 2008 at 8:49 am
Thanks to all for your help, and sorry for the lack of detail. 'Crunch time' got the best of me.
cdun2
February 27, 2008 at 9:11 am
Hey Matt,
Nice solution! A buddy of mine has turned me onto the row_number (), and I've been trying to incorporate it more into my code. I
cdun~ you're welcome... goes to show you that there are usually several different appoaches to get what you are looking for. Seems like we always start with what we know, and then try to improve the query from there. It would be interesting to see how the query plans differ for each query.
Anyway, good luck!
Todd Carrier
MCITP - Database Administrator (SQL 2008)
MCSE: Data Platform (SQL 2012)
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply