Pull Out MAX Record

  • 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

  • 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)

  • 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)

  • 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.

  • 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

  • The easiest way is to order by cout desc and grab the first record, as Todd mentioned. I would stick with this method.

  • Just saw your post. This is going to require a little more thought than an order by. Let me look at it more.

  • 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.

  • 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)

  • 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

  • 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?

  • Thanks to all for your help, and sorry for the lack of detail. 'Crunch time' got the best of me.

    cdun2

  • 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