Transact SQL – STRING_AGG – Error – “Incorrect syntax near ‘within'”

Background

Working on a script, but noticed an error when I tried to sort the data using the String_Agg within group clause.

Code

Let us reproduce using a sample data set.

SQL

Sample -01

SQL


set nocount on
go

set XACT_ABORT on
go

--select @@version as [@@version]
--go

declare @tbl TABLE
(
      [id] int not null
        identity(1,1)

    , [state] varchar(100) not null

    , [city]  varchar(100) not null

)

insert into @tbl ([state], [city])

select 'Texas', 'San Antonio'

union all

select 'Texas', 'Houston'

union all

select 'Florida', 'Miami'

union all

select 'Florida', 'Tampa Bay'

union all

select 'Connecticut', 'Hartford'

union all

select 'Connecticut', 'New Haven'

union all

select 'Connecticut', 'Bridgeport'

union all

select 'Connecticut', 'Bristol'

select
          tbl.[state]

        , [cities]

            = STRING_AGG
            (
                tbl.[city]
                , ', '
            )

from @tbl tbl

group by
        tbl.[state]

order by
        tbl.[state]

Output

incorrectSyntaxNearWithin.group.no.01.20190831.1032PM

Sample -02

Outline

If one reviews our output, one will notice that the cities are listed based on the order in which they were added.

They are not listed alphabetically.

Let us try to address that by using the group by within clause.

SQL


set nocount on
go

set XACT_ABORT on
go

--select @@version as [@@version]
--go

declare @tbl TABLE
(
      [id] int not null
        identity(1,1)

    , [state] varchar(100) not null

    , [city]  varchar(100) not null

)

insert into @tbl ([state], [city])

select 'Texas', 'San Antonio'

union all

select 'Texas', 'Houston'

union all

select 'Florida', 'Miami'

union all

select 'Florida', 'Tampa Bay'

union all

select 'Connecticut', 'Hartford'

union all

select 'Connecticut', 'New Haven'

union all

select 'Connecticut', 'Bridgeport'

union all

select 'Connecticut', 'Bristol'

select
          tbl.[state]

        , [cities]

            = STRING_AGG
            (
                tbl.[city]
                , ', '
            )

            within group
            (
                order by
                    tbl.[city]
            )

from @tbl tbl

group by
        tbl.[state]

order by
        tbl.[state]

Output

Output – Text

Msg 102, Level 15, State 1, Line 83
Incorrect syntax near 'within'.

Output – Image

incorrectSyntaxNearWithin.group.yes.01.failed.20190831.1039PM

Troubleshooting

SQL Server Version

Failing Instance

Let us issue select @@version against our instance.


select @@version

Output

Output – Text
Microsoft SQL Server 2017 (RTM-CU9-GDR) (KB4293805) - 14.0.3035.2 (X64) Jul 6 2018 18:24:36 Copyright (C) 2017 Microsoft Corporation Standard Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 (Build 9600: ) (Hypervisor)
Output – Image

incorrectSyntaxNearWithin.version.01.failed.20190831.1046PM

Remediation

Apply SQL Server Cumulative Patch.

SQL Server Version

Good Instance

Let us issue select @@version against another instance where the code works :-

Output

Output – Text
Microsoft SQL Server 2017 (RTM-CU9-GDR) (KB4293805) - 14.0.3035.2 (X64) Jul 6 2018 18:24:36 Copyright (C) 2017 Microsoft Corporation Standard Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 (Build 9600: ) (Hypervisor)
Output – Image

incorrectSyntaxNearWithin.version.01.good.20190831.1053PM

Validation

Code

 

SQL


set nocount on
go

set XACT_ABORT on
go

--select @@version as [@@version]
--go

declare @tbl TABLE
(
      [id] int not null
        identity(1,1)

    , [state] varchar(100) not null

    , [city]  varchar(100) not null

)

insert into @tbl ([state], [city])

select 'Texas', 'San Antonio'

union all

select 'Texas', 'Houston'

union all

select 'Florida', 'Miami'

union all

select 'Florida', 'Tampa Bay'

union all

select 'Connecticut', 'Hartford'

union all

select 'Connecticut', 'New Haven'

union all

select 'Connecticut', 'Bridgeport'

union all

select 'Connecticut', 'Bristol'

select
          tbl.[state]

        , [cities]

            = STRING_AGG
            (
                tbl.[city]
                , ', '
            )

from @tbl tbl

group by
        tbl.[state]

order by
        tbl.[state]

select
          tbl.[state]

        , [cities]

            = STRING_AGG
            (
                tbl.[city]
                , ', '
            )

            within group
            (
                order by
                    tbl.[city]
            )

from @tbl tbl

group by
        tbl.[state]

order by
        tbl.[state]

Output

incorrectSyntaxNearWithin.compare.badANDgood.02.20190831.1104PM.PNG

Explanation

Our cities are alphabetically ordered with each state.

Source Code Control

GitHub

TransactSQL.String_Agg/error/incorrectSyntaxNearWithin/state.city/
Link

 

Version

Please keep in mind that String_Agg is supported on SQL Server v2017 and beyond.

The version that it is failing on is :-

  1. @@VERSION
    • Microsoft SQL Server 2017 (RTM-CU9-GDR) (KB4293805) – 14.0.3035.2 (X64) Jul 6 2018 18:24:36 Copyright (C) 2017 Microsoft Corporation Standard Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: ) (Hypervisor)
      • Cummulative Patch :- RTM-CU9-GDR
      • Version Number :- 14.0.3035.2
      • Build Date :- Jul 6 2018 18:24:36

Version Verifiable Works :-

  1. @@VERSION
    • Microsoft SQL Server 2017 (RTM-CU13) (KB4466404) – 14.0.3048.4 (X64) Nov 30 2018 12:57:58 Copyright (C) 2017 Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: ) (Hypervisor)
      • Cummulative Patch :- RTM-CU13
      • Version Number :- 14.0.3048.4
      • Build Date :- Nov 30 2018 12:57:58

 

Summary

Not for everyone.

But, for those that it applies to :-

  1. Good Engineering
    • Trust, but verify
    • Patch your systems

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s