troubleshooting Question

SQL - ORDER BY list of RANGE window frame exceeded limit

Avatar of Zack
ZackFlag for Australia asked on
SQLMicrosoft SQL Server
4 Comments1 Solution11 ViewsLast Modified:
Hi EE,

I have the following query:

IF OBJECT_ID('tempdb..#BudgetTransTmp') IS NOT NULL
        DROP TABLE #BudgetTransTmp
Select  
        Row_number() over(ORDER BY YEAR(BTLine.DATE),MONTH(BTLine.DATE)) as RowNumber,
        COMBINATION.DISPLAYVALUE,
        BTLine.LedgerDimension                                      AS LedgerDimension,
        MIN(BTLine.TransactionCurrencyAmount)                       AS Amount,
        SUM(BTLine.TransactionCurrencyAmount)      
            OVER (PARTITION BY BTLine.LedgerDimension ORDER BY YEAR(BTLine.DATE),MONTH(BTLine.DATE),BTLine.LedgerDimension,COMBINATION.DISPLAYVALUE ) As SUM,
         --OVER (ORDER BY YEAR(BTLine.DATE),MONTH(BTLine.DATE),BTLine.LedgerDimension,COMBINATION.DISPLAYVALUE )   ,
        YEAR(BTLine.DATE)                                           AS Year ,
        MONTH(BTLine.DATE)                                          AS MONTH


INTO   #BudgetTransTmp
FROM   BudgetTransactionLine                    AS BTLine


    --Get Display value
    INNER JOIN DIMENSIONATTRIBUTEVALUECOMBINATION AS COMBINATION
    ON  COMBINATION.RECID                                           = BTLine.LEDGERDIMENSION
GROUP BY  
            BTLine.LedgerDimension,
            YEAR(BTLine.DATE),
            MONTH(BTLine.DATE),
         COMBINATION.DISPLAYVALUE,
         BTLine.TRANSACTIONCURRENCYAMOUNT
ORDER BY RowNumber

Error msg: Msg 8729, Level 16, State 1, Line 3
ORDER BY list of RANGE window frame has total size of 1016 bytes. The largest size supported is 900 bytes.

How would I restructure my query to fix this issue, any assistance is appreciated?

Thank you 
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 4 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros