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

• 
  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
  

  FabiolaB March 07, 2018 10:09

  - 0 +
• 
  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.

  ccparkhill April 06, 2018 11:24

  - 0 +
• 
  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
  
  
  

  FabiolaB April 06, 2018 13:01

  - 0 +
• 
  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".

  ccparkhill April 06, 2018 13:27

  - 0 +
• 
  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. 

  RichardL April 13, 2018 12:39

  - 0 +
• 
  Hi @RichardL
  I get the same result:
  

  ccparkhill April 13, 2018 12:48

  - 0 +
• 
  Hi @RichardL

  
  

  +1 from me.
  

  
  

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

  
  

  Thanks,

  Wolfgang
  

  
  
  

  wolfixx June 19, 2018 01:22

  - 0 +
• 
  Hi @ccparkhill @wolfixx
  
  Thanks for posting.
  
  We've logged this issue as SP-7078, so please keep an eye on the release note!
  
  Thanks.
  

  Tianjiao_Li June 26, 2018 10:24

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

  ccparkhill June 26, 2018 10:27

  - 0 +
• 
  @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!
  

  Tianjiao_Li June 26, 2018 10:59

  - 0 +
• 
  Thanks @Tianjiao_Li
  

  wolfixx June 28, 2018 08:45

  - 0 +
• 
  @ccparkhill  and @wolfixx
  
  I'll double check it with the dev team and update here shortly.

  Tianjiao_Li July 02, 2018 11:47

  - 0 +
• 
  @ccparkhill  and @wolfixx
  
  I'll double check it with the dev team and update here shortly.

  Tianjiao_Li July 02, 2018 11:47

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

  Russell D December 18, 2018 16:56

  - 0 +

Add comment

Please sign in to leave a comment.