ORDER BY error, need help!

When I run this query, I get an ORDER BY error "Incorrect syntax near the keyword 'ORDER'. I've bolded it below.

If I take the ORDER by out then it works correctly, but I need the ORDER BY because of the TOP 9. Any Suggestions?
Code: ( sql )

(SELECT TOP 9 trans.vchTrustee, trans.vchCaseNumber, Trans.TotalReceipts + SUM(a.mnyBalanceAmount) AS Top9Total FROM tblCase c1 LEFT JOIN tblAsset a ON c1.intCaseID = a.intCaseID LEFT JOIN (SELECT trans40.vchTrustee, trans40.vchCaseNumber, SUM(trans40.TdMnyAmount) AS TotalReceipts FROM (SELECT c.vchTrustee, c.vchCaseNumber, SUM(td.mnyAmount) AS TdMnyAmount FROM tblTransactionDetail td INNER JOIN tblTransaction t ON td.intTransactionID = t.intTransactionID INNER JOIN tblBankAccount b ON t.intAccountID = b.intAccountID INNER JOIN tblCase c ON b.intCaseID = c.intCaseID GROUP BY c.intDeleted, b.intDeleted, td.intDeleted, t.intDeleted, c.vchTrustee, c.vchCaseNumber, c.intCaseID, td.intTransactionType, t.intTransactionMethod, c.intStatus HAVING t.intDeleted = 0 AND td.intDeleted = 0 AND b.intDeleted = 0 AND c.intDeleted = 0 AND td.intTransactionType = 200 AND (t.intTransactionMethod = 40 OR t.intTransactionMethod = 41 OR t.intTransactionMethod = 43) AND c.vchTrustee = 'RLW' AND c.intStatus = 1 ) AS Trans40 GROUP BY Trans40.vchTrustee, Trans40.vchCaseNumber ) AS Trans ON c1.vchCaseNumber = Trans.vchCaseNumber GROUP BY trans.vchTrustee, trans.vchCaseNumber, a.intDeleted, c1.intDeleted, trans.TotalReceipts, c1.intStatus HAVING a.intDeleted = 0 AND c1.intDeleted = 0 AND trans.vchTrustee = 'RLW' AND c1.intStatus = 1 [B]ORDER BY (Trans.TotalReceipts + SUM(a.mnyBalanceAmount)) DESC[/B]) UNION ALL (SELECT Top9.vchTrustee, ' ', (AllCases.TR - SUM(Top9.Top9Total)) AS TRMinusTop9 FROM (SELECT AllTotalReceipts.vchTrustee, SUM(AllTotalReceipts.TotalMoney) AS TR FROM (SELECT trans.vchTrustee, trans.vchCaseNumber, Trans.TotalReceipts, SUM(a.mnyBalanceAmount) AS totalAssetBalance, (Trans.TotalReceipts + SUM(a.mnyBalanceAmount)) AS TotalMoney FROM tblCase c1 LEFT JOIN tblAsset a ON c1.intCaseID = a.intCaseID LEFT JOIN (SELECT trans40.vchTrustee, trans40.vchCaseNumber, SUM(trans40.TdMnyAmount) AS TotalReceipts FROM (SELECT c.vchTrustee, c.vchCaseNumber, SUM(td.mnyAmount) AS TdMnyAmount FROM tblTransactionDetail td INNER JOIN tblTransaction t ON td.intTransactionID = t.intTransactionID INNER JOIN tblBankAccount b ON t.intAccountID = b.intAccountID INNER JOIN tblCase c ON b.intCaseID = c.intCaseID GROUP BY c.intDeleted, b.intDeleted, td.intDeleted, t.intDeleted, c.vchTrustee, c.vchCaseNumber, c.intCaseID, td.intTransactionType, t.intTransactionMethod, c.intStatus HAVING t.intDeleted = 0 AND td.intDeleted = 0 AND b.intDeleted = 0 AND c.intDeleted = 0 AND td.intTransactionType = 200 AND (t.intTransactionMethod = 40 OR t.intTransactionMethod = 41 OR t.intTransactionMethod = 43) AND c.vchTrustee = 'RLW' AND c.intStatus = 1 ) AS Trans40 GROUP BY Trans40.vchTrustee, Trans40.vchCaseNumber ) AS Trans ON c1.vchCaseNumber = Trans.vchCaseNumber GROUP BY trans.vchTrustee, trans.vchCaseNumber, a.intDeleted, c1.intDeleted, trans.TotalReceipts, c1.intStatus HAVING a.intDeleted = 0 AND c1.intDeleted = 0 AND trans.vchTrustee = 'RLW' AND c1.intStatus = 1 ) AS AllTotalReceipts GROUP BY AllTotalReceipts.vchTrustee) AS AllCases LEFT JOIN (SELECT TOP 9 trans.vchTrustee, trans.vchCaseNumber, Trans.TotalReceipts, SUM(a.mnyBalanceAmount) AS totalAssetBalance, Trans.TotalReceipts + SUM(a.mnyBalanceAmount) AS Top9Total FROM tblCase c1 LEFT JOIN tblAsset a ON c1.intCaseID = a.intCaseID LEFT JOIN (SELECT trans40.vchTrustee, trans40.vchCaseNumber, SUM(trans40.TdMnyAmount) AS TotalReceipts FROM (SELECT c.vchTrustee, c.vchCaseNumber, SUM(td.mnyAmount) AS TdMnyAmount FROM tblTransactionDetail td INNER JOIN tblTransaction t ON td.intTransactionID = t.intTransactionID INNER JOIN tblBankAccount b ON t.intAccountID = b.intAccountID INNER JOIN tblCase c ON b.intCaseID = c.intCaseID GROUP BY c.intDeleted, b.intDeleted, td.intDeleted, t.intDeleted, c.vchTrustee, c.vchCaseNumber, c.intCaseID, td.intTransactionType, t.intTransactionMethod, c.intStatus HAVING t.intDeleted = 0 AND td.intDeleted = 0 AND b.intDeleted = 0 AND c.intDeleted = 0 AND td.intTransactionType = 200 AND (t.intTransactionMethod = 40 OR t.intTransactionMethod = 41 OR t.intTransactionMethod = 43) AND c.vchTrustee = 'RLW' AND c.intStatus = 1 ) AS Trans40 GROUP BY Trans40.vchTrustee, Trans40.vchCaseNumber ) AS Trans ON c1.vchCaseNumber = Trans.vchCaseNumber GROUP BY trans.vchTrustee, trans.vchCaseNumber, a.intDeleted, c1.intDeleted, trans.TotalReceipts, c1.intStatus HAVING a.intDeleted = 0 AND c1.intDeleted = 0 AND trans.vchTrustee = 'RLW' AND c1.intStatus = 1 ORDER BY Trans.TotalReceipts + SUM(a.mnyBalanceAmount) DESC ) AS Top9 ON AllCases.vchTrustee = Top9.vchTrustee GROUP BY Top9.vchTrustee, AllCases.TR )

Quote:

Originally Posted by speavey

When I run this query, I get an ORDER BY error "Incorrect syntax near the keyword 'ORDER'. I've bolded it below.

If I take the ORDER by out then it works correctly, but I need the ORDER BY because of the TOP 9. Any Suggestions?

(SELECT TOP 9 trans.vchTrustee, trans.vchCaseNumber, Trans.TotalReceipts + SUM(a.mnyBalanceAmount) As Top9Total

FROM tblCase c1

LEFT JOIN tblAsset a ON c1.intCaseID = a.intCaseID
LEFT JOIN
(Select trans40.vchTrustee, trans40.vchCaseNumber, SUM(trans40.TdMnyAmount) As TotalReceipts

From
(SELECT c.vchTrustee, c.vchCaseNumber, SUM(td.mnyAmount) As TdMnyAmount
FROM tblTransactionDetail td

INNER JOIN tblTransaction t ON td.intTransactionID = t.intTransactionID
INNER JOIN tblBankAccount b ON t.intAccountID = b.intAccountID
INNER JOIN tblCase c ON b.intCaseID = c.intCaseID

GROUP BY c.intDeleted, b.intDeleted, td.intDeleted, t.intDeleted,
c.vchTrustee, c.vchCaseNumber, c.intCaseID, td.intTransactionType,
t.intTransactionMethod, c.intStatus

HAVING t.intDeleted = 0 AND td.intDeleted = 0 AND b.intDeleted = 0 AND c.intDeleted = 0
AND td.intTransactionType = 200
AND (t.intTransactionMethod = 40 OR t.intTransactionMethod = 41 OR t.intTransactionMethod = 43)
AND c.vchTrustee = 'RLW' AND c.intStatus = 1

) As Trans40

GROUP BY Trans40.vchTrustee, Trans40.vchCaseNumber
) As Trans ON c1.vchCaseNumber = Trans.vchCaseNumber

GROUP BY trans.vchTrustee, trans.vchCaseNumber, a.intDeleted, c1.intDeleted, trans.TotalReceipts, c1.intStatus

HAVING a.intDeleted = 0 AND c1.intDeleted = 0 AND trans.vchTrustee = 'RLW' AND c1.intStatus = 1

ORDER BY (Trans.TotalReceipts + SUM(a.mnyBalanceAmount)) DESC)

UNION All

(SELECT Top9.vchTrustee, ' ', (AllCases.TR - SUM(Top9.Top9Total)) As TRMinusTop9
FROM

(SELECT AllTotalReceipts.vchTrustee, SUM(AllTotalReceipts.TotalMoney) As TR
FROM

(SELECT trans.vchTrustee, trans.vchCaseNumber, Trans.TotalReceipts, SUM(a.mnyBalanceAmount) As totalAssetBalance,
(Trans.TotalReceipts + SUM(a.mnyBalanceAmount)) As TotalMoney

FROM tblCase c1

LEFT JOIN tblAsset a ON c1.intCaseID = a.intCaseID
LEFT JOIN
(Select trans40.vchTrustee, trans40.vchCaseNumber, SUM(trans40.TdMnyAmount) As TotalReceipts

From
(SELECT c.vchTrustee, c.vchCaseNumber, SUM(td.mnyAmount) As TdMnyAmount
FROM tblTransactionDetail td
INNER JOIN tblTransaction t ON td.intTransactionID = t.intTransactionID
INNER JOIN tblBankAccount b ON t.intAccountID = b.intAccountID
INNER JOIN tblCase c ON b.intCaseID = c.intCaseID

GROUP BY c.intDeleted, b.intDeleted, td.intDeleted, t.intDeleted,
c.vchTrustee, c.vchCaseNumber, c.intCaseID, td.intTransactionType,
t.intTransactionMethod, c.intStatus

HAVING t.intDeleted = 0 AND td.intDeleted = 0 AND b.intDeleted = 0 AND c.intDeleted = 0
AND td.intTransactionType = 200
AND (t.intTransactionMethod = 40 OR t.intTransactionMethod = 41 OR t.intTransactionMethod = 43)
AND c.vchTrustee = 'RLW' AND c.intStatus = 1

) As Trans40

GROUP BY Trans40.vchTrustee, Trans40.vchCaseNumber
) As Trans ON c1.vchCaseNumber = Trans.vchCaseNumber

GROUP BY trans.vchTrustee, trans.vchCaseNumber, a.intDeleted, c1.intDeleted, trans.TotalReceipts, c1.intStatus
HAVING a.intDeleted = 0 AND c1.intDeleted = 0 AND trans.vchTrustee = 'RLW' AND c1.intStatus = 1
) As AllTotalReceipts

GROUP BY AllTotalReceipts.vchTrustee) As AllCases

LEFT JOIN

(SELECT TOP 9 trans.vchTrustee, trans.vchCaseNumber, Trans.TotalReceipts, SUM(a.mnyBalanceAmount) As totalAssetBalance,
Trans.TotalReceipts + SUM(a.mnyBalanceAmount) As Top9Total

FROM tblCase c1

LEFT JOIN tblAsset a ON c1.intCaseID = a.intCaseID
LEFT JOIN
(Select trans40.vchTrustee, trans40.vchCaseNumber, SUM(trans40.TdMnyAmount) As TotalReceipts

From
(SELECT c.vchTrustee, c.vchCaseNumber, SUM(td.mnyAmount) As TdMnyAmount
FROM tblTransactionDetail td

INNER JOIN tblTransaction t ON td.intTransactionID = t.intTransactionID
INNER JOIN tblBankAccount b ON t.intAccountID = b.intAccountID
INNER JOIN tblCase c ON b.intCaseID = c.intCaseID

GROUP BY c.intDeleted, b.intDeleted, td.intDeleted, t.intDeleted,
c.vchTrustee, c.vchCaseNumber, c.intCaseID, td.intTransactionType,
t.intTransactionMethod, c.intStatus

HAVING t.intDeleted = 0 AND td.intDeleted = 0 AND b.intDeleted = 0 AND c.intDeleted = 0
AND td.intTransactionType = 200 AND (t.intTransactionMethod = 40 OR t.intTransactionMethod = 41 OR t.intTransactionMethod = 43)
AND c.vchTrustee = 'RLW' AND c.intStatus = 1

) As Trans40

GROUP BY Trans40.vchTrustee, Trans40.vchCaseNumber
) As Trans ON c1.vchCaseNumber = Trans.vchCaseNumber

GROUP BY trans.vchTrustee, trans.vchCaseNumber, a.intDeleted, c1.intDeleted, trans.TotalReceipts, c1.intStatus

HAVING a.intDeleted = 0 AND c1.intDeleted = 0 AND trans.vchTrustee = 'RLW' AND c1.intStatus = 1
ORDER BY Trans.TotalReceipts + SUM(a.mnyBalanceAmount) DESC
) As Top9

ON AllCases.vchTrustee = Top9.vchTrustee
GROUP By Top9.vchTrustee, AllCases.TR

)

i believe ORDER BY has to be placed on the outermost QUERY, resulting in a sorted final returned resultset|||

Quote:

Originally Posted by ck9663

i believe ORDER BY has to be placed on the outermost QUERY, resulting in a sorted final returned resultset

I tried that and it won't produce the correct order by, because the First query with the TOP 9 isn't being sorted.

So if I put it on the outermost query, it doesn't sort correctly.

Maybe you can help me with my query, its very long and probably too much code. I'm trying to create a query the will SUM a value from two tables and pull the TOP 9 cases. Then also add a TOTAL SUM value - Top 9 Sum Value.

Any help would be great!!