Zack
asked on
SQL - ORDER BY list of RANGE window frame exceeded limit
Hi EE,
I have the following query:
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
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 3ORDER 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
Hi Ryan,
I would like to try to keep the partition if I could for sorting purposes.
Thank you.
I would like to try to keep the partition if I could for sorting purposes.
Thank you.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Can't argue that point :)
Open in new window
with:
Open in new window