T-SQL - how to get around the order by restriction in CTEs
- by David
Hi all
I have the following CTE. Its purpose is to provide unique Month/Year pairs. Later code will use the CTE to produce a concatenated string list of the Month/Year pairs.
;WITH tblStoredWillsInPeriod AS
(
SELECT DISTINCT Kctc.GetMonthAndYearString(DateWillReceived) Month
FROM Kctc.StoredWills
WHERE DateWillReceived BETWEEN '2010/01/01' AND '2010/03/31'
ORDER BY DateWillReceived
)
I have omitted the implmementation of the GetMonthAndYearString function as it is trivial.
Sadly, it seems T-SQL is always one step ahead. When I run this code, it tells me I'm not allowed to use ORDER BY in a CTE unless I also use TOP (or FOR XML, whatever that is.) If I use TOP, it tells me I can't use it with DISTINCT. Yup, T-SQL has all the answers.
Can anyone think of a solution to this problem which is quicker than simply slashing my wrists? I understand that death from blood loss can be surprisingly lingering, and I have deadlines to meet.
Thanks for your help.
David