"WITHIN GROUP" order clause is not supported for STRING_AGG aggregate

henrik_ffc

SQL Prompt reports an error when I try to format a statement including STRING_AGG with the optional WITHIN GROUP order clause
(https://docs.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql)

FabiolaB

Hi @henrik_ffc,

Thank you for reporting this!
We have reproduced the issue and have created a ticket for this issue(ref SP-6847).

Regards,
Fabiola

ccparkhill

The ORDER BY of the WITHIN GROUP seems to be treated as a query ORDER BY and does not get indented inline with STRING_AGG/WITHIN GROUP.

FabiolaB

Hi @ccparkhill,

Thank you for reporting this issue. 
What version of Prompt are you on? Normally, the issue has been fixed starting with version 9.1.4. (SP-6847)
Can you try downloading the latest version?

Thank you for letting us know if it works for you now.

Kind regards,
Fabiola

ccparkhill

I am using 9.1.6, this is what I get when I "Format SQL":

SELECT

    [town]

  , STRING_AGG([email], ';') WITHIN GROUP(

ORDER BY [email] ASC) AS [emails]

FROM [dbo].[Employee]

GROUP BY [town];

I would expect STRING_AGG to be treated in the same way as RANK:

SELECT

    [town]

  , STRING_AGG([email], ';') WITHIN GROUP(ORDER BY [email] ASC) AS [emails]

  , RANK() OVER (PARTITION BY [town] ORDER BY [email]) AS [RNK]

FROM [dbo].[Employee]

GROUP BY [town];

I don't get an error, I just don't get the expected result when I "Format SQL".

RichardL

Hi @ccparkhill

Thanks for your post. 

Can you try 9.1.7 below please? 

ftp://support.red-gate.com/patches/SQLPrompt/11Apr2018/SQLPrompt_9.1.7.4764.exe

Best

Richard Lynch. 

ccparkhill

Hi @RichardL
I get the same result:

wolfixx

Hi @RichardL

+1 from me.

Can you please get the order by handled correctly when within a within group clause?

Thanks,

Wolfgang

Tianjiao_Li

Hi @ccparkhill @wolfixx

Thanks for posting.

We've logged this issue as SP-7078, so please keep an eye on the release note!

Thanks.

ccparkhill

Thanks @Tianjiao_Li, I believe this was already logged under SP-6941.

Tianjiao_Li

@ccparkhill

Thanks! You are right! I've closed SP-7078. Sorry for it. Hi @wolfixx, please note SP-6941 as the bug reference and keep an eye on the release note!

wolfixx

Thanks @Tianjiao_Li

Tianjiao_Li

@ccparkhill  and @wolfixx

I'll double check it with the dev team and update here shortly.

Tianjiao_Li

@ccparkhill  and @wolfixx

I'll double check it with the dev team and update here shortly.

Russell D

After reviewing this I'm afraid that we're not going to dedicate more time to fixing it at this point in time.