T-SQL - how to get around the order by restriction in CTEs

Question

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.

Edit: As requested by Martin, here is the surrounding code:

DECLARE @PivotColumnHeaders nvarchar(MAX)
--CTE declaration as above---
SELECT @PivotColumnHeaders =
  COALESCE(
    @PivotColumnHeaders + ',[' + Month + ']',
    '[' + Month + ']'
  )
FROM tblStoredWillsInPeriod
SELECT @PivotColumnHeaders

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

Answer 1

Could you clarify why you need the data in the the CTE to be ordered? And why you are not able to order the data in the query using the CTE. Remember data in an ordinary subquery can't be ordered either.

Answer 2

What about?

;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 
),
tblStoredWillsInPeriodOrdered AS
(
    SELECT TOP 100 PERCENT Month 
    FROM tblStoredWillsInPeriod 
    ORDER BY Month
)

Answer 3

SELECT DISTINCT TOP 100 PERCENT ...
ORDER BY ...

Answer 4

And you think you know T-SQL syntax!

Turns out I was wrong about not being able to use TOP and DISTINCT together.

This yields a syntax error...

SELECT TOP 100 PERCENT DISTINCT...

whereas this is absolutely fine...

SELECT DISTINCT TOP 100 PERCENT...

Work that one out.

One drawback is that you have to include the ORDER BY field in the SELECT list, which in all likelihood will interfere with your expected DISTINCT results. Sometimes T-SQL has you running around in circles.

But for now, my wrists are left unmarked.

Answer 5

Will this work?

DECLARE @PivotColumnHeaders VARCHAR(MAX)

;WITH StoredWills AS
(
SELECT GETDATE() AS DateWillReceived
UNION ALL
SELECT '2010-03-14 11:48:07.580'
UNION ALL
SELECT '2010-03-12 11:48:07.580'
UNION ALL
SELECT '2010-02-12 11:48:07.580'
),
tblStoredWillsInPeriod AS
(
    SELECT DISTINCT STUFF(RIGHT(convert(VARCHAR, DateWillReceived, 106),8), 4, 1, '-') AS  MMMYYYY, 
    DatePart(Year,DateWillReceived) AS  Year,
    DatePart(Month,DateWillReceived) AS  Month
    FROM StoredWills
    WHERE DateWillReceived BETWEEN '2010-01-01' AND '2010-03-31'
)


SELECT @PivotColumnHeaders =
  COALESCE(
    @PivotColumnHeaders + ',[' + MMMYYYY + ']',
    '[' + MMMYYYY + ']'
  )
FROM tblStoredWillsInPeriod
ORDER BY Year, Month