Post reply

ORDER BY on Aggregate

  • January 29, 2013 at 12:35 pm

    #273530

    I need to order the following Query by the auditdate field, which is unfortunately a MAX. Anybody have a suggestion on how I can go about doing that? Thanks.

    select distinct S.agentNumber, max(S.auditdate) as auditdate, A.agencyName, A.state, A.region, A.Parent from tblAudit_Auditschedule S left outer join tblAgents A On A.agentNumber = s.agentNumber left outer join tblAgentStates R ON A.agentNumber = R.agentNumber

  • January 29, 2013 at 12:46 pm

    #1582320

    Subquery it. IE:

    SELECT * FROM

    ( SELECT max(abc) AS maxABC FROM #table)

    ORDER BY

    maxABC

    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
    Twitter: @AnyWayDBA

  • January 29, 2013 at 1:08 pm

    #1582336

    Evil Kraig F (1/29/2013)

    Subquery it. IE:

    SELECT * FROM

    ( SELECT max(abc) AS maxABC FROM #table)

    ORDER BY

    maxABC

    Thanks for the advice, but I'm confused on where I am supposed to sub-query.

  • January 29, 2013 at 1:11 pm

    #1582342

    EDIT: That query can't run as is, need to adjust for the group by.

    Ah, apologies, I could have worded that a LOT more clearly. Easy enough. You take your entire query and you wrap it as a subtable of an outer query, like so:

    SELECT

    *

    FROM

    (select

    S.agentNumber,

    max(S.auditdate) as auditdate,

    A.agencyName,

    A.state,

    A.region,

    A.Parent

    from

    tblAudit_Auditschedule S

    left outer join

    tblAgents A

    OnA.agentNumber = s.agentNumber

    left outer join

    tblAgentStates R

    ONA.agentNumber = R.agentNumber

    GROUP BY

    S.agentNumber,

    A.agencyName,

    A.state,

    A.region,

    A.Parent

    ) AS drv

    ORDER BY

    auditdate

    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
    Twitter: @AnyWayDBA

  • January 29, 2013 at 1:16 pm

    #1582348

    Shouldn't this work?

    SELECT S.agentNumber,

    MAX(S.auditdate) as auditdate,

    A.agencyName,

    A.state,

    A.region,

    A.Parent

    FROM tblAudit_Auditschedule S

    LEFT OUTER JOIN tblAgents A On A.agentNumber = s.agentNumber

    LEFT OUTER JOIN tblAgentStates R ON A.agentNumber = R.agentNumber

    GROUP BY S.agentNumber,

    A.agencyName,

    A.state,

    A.region,

    A.Parent

    ORDER BY MAX( s.auditdate)

    Or even using the alias.

    By the way, the DISTINCT clause is not needed.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • January 29, 2013 at 1:17 pm

    #1582350

    Don't even need a subquery. This should be perfectly legal code

    select

    S.agentNumber,

    max(S.auditdate) as auditdate,

    A.agencyName,

    A.state,

    A.region,

    A.Parent

    from

    tblAudit_Auditschedule S

    left outer join

    tblAgents A

    OnA.agentNumber = s.agentNumber

    left outer join

    tblAgentStates R

    ONA.agentNumber = R.agentNumber

    GROUP BY

    S.agentNumber,

    A.agencyName,

    A.state,

    A.region,

    A.Parent

    ORDER BY max(S.auditdate)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 29, 2013 at 1:20 pm

    #1582353

    :blush:Yeah, that works too guys. Sorry about that, I'm chalking that one up to cold meds and a different DB system mixing up my head. Well, now the OP has two ways to do it. 🙂

    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
    Twitter: @AnyWayDBA

  • January 29, 2013 at 1:41 pm

    #1582367

    Thanks for all the help guys, that sorted me right out.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply