Revisions to Why is my ORDER BY in STRING_AGG not always working?

improve code format per syntax

Source Link

edited Oct 15, 2022 at 10:49

86.5k
15
252
342

I have a table that consists of a record ID, a group ID (linking 1 or more records into a group) and a hash value for each record.

CREATE TABLE HashTable(
    RecordID VARCHAR(255),
    GroupIdentifier VARCHAR(255),
    Hash VARCHAR (255),
    GroupHashList VARCHAR(4000)
)

(I know this isn't an efficient table, but it's just a temp table for the purposes of this example).

I want to generate a hash for each group, so I thought the simplest way would be to concatenate the hashes of each record in the group. RecordIDs are unique, but what those records relate to are not necessarily unique so the hashes may be duplicates. The point of this is to flag fully duplicate groups ie a group were all records in that group are duplicates of all records in another group. The GUI needs all members of the group to have the same hash if it's to recognise them as a duplicate group.

I'm using STRING_AGG to concatenate the individual hashes of the records in the group, and sorting them by the hash to make sure I get the same string of characters for duplicate groups. I don't actually care what the order of the hashes is, so long as it's the same each time. When I run it as a SELECT query, it works fine and I can see identical strings for duplicate groups. When I take that same SELECT query and put it into an UPDATE query, the ordering seems to get lost.

SELECT STRING_AGG([Hash],';') WITHIN GROUP (ORDER BY [Hash] ASC), 
FROM HashTable
GROUP BY [GroupIdentifier]

This gives the results (for an example pair of duplicate groups):

73F294873462B2BA0E930FD16DCCB7;90E749375DF806CB6E3F5CA48FFA38;E44256CE7CFCB971EB679BAC25A697
73F294873462B2BA0E930FD16DCCB7;90E749375DF806CB6E3F5CA48FFA38;E44256CE7CFCB971EB679BAC25A697

When I put that same code into the UPDATE query, it doesn't sort them correctly:

UPDATE HashTable
SET GroupHashList = c.HashList
FROM HashTable
INNER JOIN (
    SELECT (STRING_AGG([Hash],';') WITHIN GROUP (ORDER BY [Hash] ASC)) AS [HashList],
        [GroupIdentifier]
    FROM HashTable
    GROUP BY [GroupIdentifier]) c
ON c.[GroupIdentifier] = HashTable.[GroupIdentifier]

Results for the same two groups that gets written to the table:

73F294873462B2BA0E930FD16DCCB7;90E749375DF806CB6E3F5CA48FFA38;E44256CE7CFCB971EB679BAC25A697
73F294873462B2BA0E930FD16DCCB7;E44256CE7CFCB971EB679BAC25A697;90E749375DF806CB6E3F5CA48FFA38

What am I missing?

What I'm getting the first time is

Hash1; Hash2; Hash3
Hash1; Hash2; Hash3

But when it's in the UPDATE query, I get

Hash1; Hash2; Hash3
Hash1; Hash3; Hash2

The update query is sorted by record ID, although I don't know if that's coincidence. (https://dbfiddle.uk/CPG1-z2l)

I have a table that consists of a record ID, a group ID (linking 1 or more records into a group) and a hash value for each record.

CREATE TABLE HashTable(
    RecordID VARCHAR(255),
    GroupIdentifier VARCHAR(255),
    Hash VARCHAR (255),
    GroupHashList VARCHAR(4000)
)

(I know this isn't an efficient table, but it's just a temp table for the purposes of this example).

I want to generate a hash for each group, so I thought the simplest way would be to concatenate the hashes of each record in the group. RecordIDs are unique, but what those records relate to are not necessarily unique so the hashes may be duplicates. The point of this is to flag fully duplicate groups ie a group were all records in that group are duplicates of all records in another group. The GUI needs all members of the group to have the same hash if it's to recognise them as a duplicate group.

I'm using STRING_AGG to concatenate the individual hashes of the records in the group, and sorting them by the hash to make sure I get the same string of characters for duplicate groups. I don't actually care what the order of the hashes is, so long as it's the same each time. When I run it as a SELECT query, it works fine and I can see identical strings for duplicate groups. When I take that same SELECT query and put it into an UPDATE query, the ordering seems to get lost.

SELECT STRING_AGG([Hash],';') WITHIN GROUP (ORDER BY [Hash] ASC), 
FROM HashTable
GROUP BY [GroupIdentifier]

This gives the results (for an example pair of duplicate groups):

73F294873462B2BA0E930FD16DCCB7;90E749375DF806CB6E3F5CA48FFA38;E44256CE7CFCB971EB679BAC25A697
73F294873462B2BA0E930FD16DCCB7;90E749375DF806CB6E3F5CA48FFA38;E44256CE7CFCB971EB679BAC25A697

When I put that same code into the UPDATE query, it doesn't sort them correctly:

UPDATE HashTable
SET GroupHashList = c.HashList
FROM HashTable
INNER JOIN (
    SELECT (STRING_AGG([Hash],';') WITHIN GROUP (ORDER BY [Hash] ASC)) AS [HashList],
        [GroupIdentifier]
    FROM HashTable
    GROUP BY [GroupIdentifier]) c
ON c.[GroupIdentifier] = HashTable.[GroupIdentifier]

Results for the same two groups that gets written to the table:

73F294873462B2BA0E930FD16DCCB7;90E749375DF806CB6E3F5CA48FFA38;E44256CE7CFCB971EB679BAC25A697
73F294873462B2BA0E930FD16DCCB7;E44256CE7CFCB971EB679BAC25A697;90E749375DF806CB6E3F5CA48FFA38

What am I missing?

What I'm getting the first time is

Hash1; Hash2; Hash3
Hash1; Hash2; Hash3

But when it's in the UPDATE query, I get

Hash1; Hash2; Hash3
Hash1; Hash3; Hash2

The update query is sorted by record ID, although I don't know if that's coincidence. (https://dbfiddle.uk/CPG1-z2l)

I have a table that consists of a record ID, a group ID (linking 1 or more records into a group) and a hash value for each record.

CREATE TABLE HashTable(
    RecordID VARCHAR(255),
    GroupIdentifier VARCHAR(255),
    Hash VARCHAR (255),
    GroupHashList VARCHAR(4000)
)

(I know this isn't an efficient table, but it's just a temp table for the purposes of this example).

I want to generate a hash for each group, so I thought the simplest way would be to concatenate the hashes of each record in the group. RecordIDs are unique, but what those records relate to are not necessarily unique so the hashes may be duplicates. The point of this is to flag fully duplicate groups ie a group were all records in that group are duplicates of all records in another group. The GUI needs all members of the group to have the same hash if it's to recognise them as a duplicate group.

I'm using STRING_AGG to concatenate the individual hashes of the records in the group, and sorting them by the hash to make sure I get the same string of characters for duplicate groups. I don't actually care what the order of the hashes is, so long as it's the same each time. When I run it as a SELECT query, it works fine and I can see identical strings for duplicate groups. When I take that same SELECT query and put it into an UPDATE query, the ordering seems to get lost.

SELECT STRING_AGG([Hash],';') WITHIN GROUP (ORDER BY [Hash] ASC) 
FROM HashTable
GROUP BY [GroupIdentifier]

This gives the results (for an example pair of duplicate groups):

73F294873462B2BA0E930FD16DCCB7;90E749375DF806CB6E3F5CA48FFA38;E44256CE7CFCB971EB679BAC25A697
73F294873462B2BA0E930FD16DCCB7;90E749375DF806CB6E3F5CA48FFA38;E44256CE7CFCB971EB679BAC25A697

When I put that same code into the UPDATE query, it doesn't sort them correctly:

UPDATE HashTable
SET GroupHashList = c.HashList
FROM HashTable
INNER JOIN (
    SELECT (STRING_AGG([Hash],';') WITHIN GROUP (ORDER BY [Hash] ASC)) AS [HashList],
        [GroupIdentifier]
    FROM HashTable
    GROUP BY [GroupIdentifier]) c
ON c.[GroupIdentifier] = HashTable.[GroupIdentifier]

Results for the same two groups that gets written to the table:

73F294873462B2BA0E930FD16DCCB7;90E749375DF806CB6E3F5CA48FFA38;E44256CE7CFCB971EB679BAC25A697
73F294873462B2BA0E930FD16DCCB7;E44256CE7CFCB971EB679BAC25A697;90E749375DF806CB6E3F5CA48FFA38

What am I missing?

What I'm getting the first time is

Hash1; Hash2; Hash3
Hash1; Hash2; Hash3

But when it's in the UPDATE query, I get

Hash1; Hash2; Hash3
Hash1; Hash3; Hash2

The update query is sorted by record ID, although I don't know if that's coincidence. (https://dbfiddle.uk/CPG1-z2l)

improve code format per syntax

Source Link

edit approved Oct 15, 2022 at 10:49

philipxy

777
1
7
18

I have a table that consists of a record ID, a group ID (linking 1 or more records into a group) and a hash value for each record.

CREATE TABLE HashTable(
    RecordID VARCHAR(255),
    GroupIdentifier VARCHAR(255),
    Hash VARCHAR (255),
    GroupHashList VARCHAR(4000)
)

(I know this isn't an efficient table, but it's just a temp table for the purposes of this example).

I want to generate a hash for each group, so I thought the simplest way would be to concatenate the hashes of each record in the group. RecordIDs are unique, but what those records relate to are not necessarily unique so the hashes may be duplicates. The point of this is to flag fully duplicate groups ie a group were all records in that group are duplicates of all records in another group. The GUI needs all members of the group to have the same hash if it's to recognise them as a duplicate group.

I'm using STRING_AGG to concatenate the individual hashes of the records in the group, and sorting them by the hash to make sure I get the same string of characters for duplicate groups. I don't actually care what the order of the hashes is, so long as it's the same each time. When I run it as a SELECT query, it works fine and I can see identical strings for duplicate groups. When I take that same SELECT query and put it into an UPDATE query, the ordering seems to get lost.

SELECT STRING_AGG([Hash],';') WITHIN GROUP (ORDER BY [Hash] ASC), 
    FROM HashTable
    GROUP BY [GroupIdentifier]

This gives the results (for an example pair of duplicate groups):

73F294873462B2BA0E930FD16DCCB7;90E749375DF806CB6E3F5CA48FFA38;E44256CE7CFCB971EB679BAC25A697
73F294873462B2BA0E930FD16DCCB7;90E749375DF806CB6E3F5CA48FFA38;E44256CE7CFCB971EB679BAC25A697

When I put that same code into the UPDATE query, it doesn't sort them correctly:

UPDATE HashTable
SET GroupHashList = c.HashList
FROM HashTable  
INNER JOIN (
    SELECT (STRING_AGG([Hash],';') WITHIN GROUP (ORDER BY [Hash] ASC)) AS [HashList],
        [GroupIdentifier]
    FROM HashTable
    GROUP BY [GroupIdentifier]) c
    ON c.[GroupIdentifier] = HashTable.[GroupIdentifier]

Results for the same two groups that gets written to the table:

73F294873462B2BA0E930FD16DCCB7;90E749375DF806CB6E3F5CA48FFA38;E44256CE7CFCB971EB679BAC25A697
73F294873462B2BA0E930FD16DCCB7;E44256CE7CFCB971EB679BAC25A697;90E749375DF806CB6E3F5CA48FFA38

What am I missing?

What I'm getting the first time is

Hash1; Hash2; Hash3
Hash1; Hash2; Hash3

But when it's in the UPDATE query, I get

Hash1; Hash2; Hash3
Hash1; Hash3; Hash2

The update query is sorted by record ID, although I don't know if that's coincidence. (https://dbfiddle.uk/CPG1-z2l)

I have a table that consists of a record ID, a group ID (linking 1 or more records into a group) and a hash value for each record.

CREATE TABLE HashTable(
    RecordID VARCHAR(255),
    GroupIdentifier VARCHAR(255),
    Hash VARCHAR (255),
    GroupHashList VARCHAR(4000)
)

(I know this isn't an efficient table, but it's just a temp table for the purposes of this example).

I want to generate a hash for each group, so I thought the simplest way would be to concatenate the hashes of each record in the group. RecordIDs are unique, but what those records relate to are not necessarily unique so the hashes may be duplicates. The point of this is to flag fully duplicate groups ie a group were all records in that group are duplicates of all records in another group. The GUI needs all members of the group to have the same hash if it's to recognise them as a duplicate group.

I'm using STRING_AGG to concatenate the individual hashes of the records in the group, and sorting them by the hash to make sure I get the same string of characters for duplicate groups. I don't actually care what the order of the hashes is, so long as it's the same each time. When I run it as a SELECT query, it works fine and I can see identical strings for duplicate groups. When I take that same SELECT query and put it into an UPDATE query, the ordering seems to get lost.

SELECT STRING_AGG([Hash],';') WITHIN GROUP (ORDER BY [Hash] ASC), 
    FROM HashTable
    GROUP BY [GroupIdentifier]

This gives the results (for an example pair of duplicate groups):

73F294873462B2BA0E930FD16DCCB7;90E749375DF806CB6E3F5CA48FFA38;E44256CE7CFCB971EB679BAC25A697
73F294873462B2BA0E930FD16DCCB7;90E749375DF806CB6E3F5CA48FFA38;E44256CE7CFCB971EB679BAC25A697

When I put that same code into the UPDATE query, it doesn't sort them correctly:

UPDATE HashTable
SET GroupHashList = c.HashList
FROM HashTable INNER JOIN (SELECT (STRING_AGG([Hash],';') WITHIN GROUP (ORDER BY [Hash] ASC)) AS [HashList],
    [GroupIdentifier]
    FROM HashTable
    GROUP BY [GroupIdentifier]) c
    ON c.[GroupIdentifier] = HashTable.[GroupIdentifier]

Results for the same two groups that gets written to the table:

73F294873462B2BA0E930FD16DCCB7;90E749375DF806CB6E3F5CA48FFA38;E44256CE7CFCB971EB679BAC25A697
73F294873462B2BA0E930FD16DCCB7;E44256CE7CFCB971EB679BAC25A697;90E749375DF806CB6E3F5CA48FFA38

What am I missing?

What I'm getting the first time is

Hash1; Hash2; Hash3
Hash1; Hash2; Hash3

But when it's in the UPDATE query, I get

Hash1; Hash2; Hash3
Hash1; Hash3; Hash2

The update query is sorted by record ID, although I don't know if that's coincidence. (https://dbfiddle.uk/CPG1-z2l)

I have a table that consists of a record ID, a group ID (linking 1 or more records into a group) and a hash value for each record.

CREATE TABLE HashTable(
    RecordID VARCHAR(255),
    GroupIdentifier VARCHAR(255),
    Hash VARCHAR (255),
    GroupHashList VARCHAR(4000)
)

(I know this isn't an efficient table, but it's just a temp table for the purposes of this example).

I want to generate a hash for each group, so I thought the simplest way would be to concatenate the hashes of each record in the group. RecordIDs are unique, but what those records relate to are not necessarily unique so the hashes may be duplicates. The point of this is to flag fully duplicate groups ie a group were all records in that group are duplicates of all records in another group. The GUI needs all members of the group to have the same hash if it's to recognise them as a duplicate group.

I'm using STRING_AGG to concatenate the individual hashes of the records in the group, and sorting them by the hash to make sure I get the same string of characters for duplicate groups. I don't actually care what the order of the hashes is, so long as it's the same each time. When I run it as a SELECT query, it works fine and I can see identical strings for duplicate groups. When I take that same SELECT query and put it into an UPDATE query, the ordering seems to get lost.

SELECT STRING_AGG([Hash],';') WITHIN GROUP (ORDER BY [Hash] ASC), 
FROM HashTable
GROUP BY [GroupIdentifier]

This gives the results (for an example pair of duplicate groups):

73F294873462B2BA0E930FD16DCCB7;90E749375DF806CB6E3F5CA48FFA38;E44256CE7CFCB971EB679BAC25A697
73F294873462B2BA0E930FD16DCCB7;90E749375DF806CB6E3F5CA48FFA38;E44256CE7CFCB971EB679BAC25A697

When I put that same code into the UPDATE query, it doesn't sort them correctly:

UPDATE HashTable
SET GroupHashList = c.HashList
FROM HashTable 
INNER JOIN (
    SELECT (STRING_AGG([Hash],';') WITHIN GROUP (ORDER BY [Hash] ASC)) AS [HashList],
        [GroupIdentifier]
    FROM HashTable
    GROUP BY [GroupIdentifier]) c
ON c.[GroupIdentifier] = HashTable.[GroupIdentifier]

Results for the same two groups that gets written to the table:

73F294873462B2BA0E930FD16DCCB7;90E749375DF806CB6E3F5CA48FFA38;E44256CE7CFCB971EB679BAC25A697
73F294873462B2BA0E930FD16DCCB7;E44256CE7CFCB971EB679BAC25A697;90E749375DF806CB6E3F5CA48FFA38

What am I missing?

What I'm getting the first time is

Hash1; Hash2; Hash3
Hash1; Hash2; Hash3

But when it's in the UPDATE query, I get

Hash1; Hash2; Hash3
Hash1; Hash3; Hash2

The update query is sorted by record ID, although I don't know if that's coincidence. (https://dbfiddle.uk/CPG1-z2l)

removed meta & social content, improved language

Source Link

edit approved Oct 15, 2022 at 9:30

philipxy

777
1
7
18

Updated in response to comments.

I have a table that consists of a record ID, a group ID (linking 1 or more records into a group) and a hash value for each record.

CREATE TABLE HashTable(
    RecordID VARCHAR(255),
    GroupIdentifier VARCHAR(255),
    Hash VARCHAR (255),
    GroupHashList VARCHAR(4000)
)

(I know this isn't an efficient table, but it's just a temp table for the purposes of this example).

I want to generate a hash for each group, so I thought the simplest way would be to concatenate the hashes of each record in the group. RecordIDs are unique, but what those records relate to are not necessarily unique so the hashes may be duplicates. The point of this is to flag fully duplicativeduplicate groups ie a group were all records in that group are duplicates of all records in another group. The GUI needs all members of the group to have the same hash if it's to recognise them as a duplicate group.

I'm using STRING_AGG to concatenate the individual hashes of the records in the group, and sorting them by the hash to make sure I get the same string of characters for duplicate groups. I don't actually care what the order of the hashes is, so long as it's the same each time. When I run it as a SELECT query, it works fine and I can see identical strings for duplicate groups. When I take that same SELECT query and put it into an UPDATE query, the ordering seems to get lost.

SELECT query:

SELECT STRING_AGG([Hash],';') WITHIN GROUP (ORDER BY [Hash] ASC), 
    FROM HashTable
    GROUP BY [GroupIdentifier]

This gives the results (for an example pair of duplicate groups):

73F294873462B2BA0E930FD16DCCB7;90E749375DF806CB6E3F5CA48FFA38;E44256CE7CFCB971EB679BAC25A697
73F294873462B2BA0E930FD16DCCB7;90E749375DF806CB6E3F5CA48FFA38;E44256CE7CFCB971EB679BAC25A697

When I put that same code into the UPDATE query, it doesn't sort them correctly:

UPDATE HashTable
SET GroupHashList = c.HashList
FROM HashTable INNER JOIN (SELECT (STRING_AGG([Hash],';') WITHIN GROUP (ORDER BY [Hash] ASC)) AS [HashList],
    [GroupIdentifier]
    FROM HashTable
    GROUP BY [GroupIdentifier]) c
    ON c.[GroupIdentifier] = HashTable.[GroupIdentifier]

Results for the same two groups that gets written to the table:

73F294873462B2BA0E930FD16DCCB7;90E749375DF806CB6E3F5CA48FFA38;E44256CE7CFCB971EB679BAC25A697
73F294873462B2BA0E930FD16DCCB7;E44256CE7CFCB971EB679BAC25A697;90E749375DF806CB6E3F5CA48FFA38

This is driving me crazy - whatWhat am I missing?

ETA Yes, the hashes are ugly to read. Basically whatWhat I'm getting the first time is

Hash1; Hash2; Hash3
Hash1; Hash2; Hash3

But when it's in the UPDATE query, I get

Hash1; Hash2; Hash3
Hash1; Hash3; Hash2

The update query is actually sorted by record ID, although I don't know if that's coincidence. (https://dbfiddle.uk/CPG1-z2l)

Updated in response to comments.

I have a table that consists of a record ID, a group ID (linking 1 or more records into a group) and a hash value for each record.

CREATE TABLE HashTable(
    RecordID VARCHAR(255),
    GroupIdentifier VARCHAR(255),
    Hash VARCHAR (255),
    GroupHashList VARCHAR(4000)
)

(I know this isn't an efficient table, but it's just a temp table for the purposes of this example).

I want to generate a hash for each group, so I thought the simplest way would be to concatenate the hashes of each record in the group. RecordIDs are unique, but what those records relate to are not necessarily unique so the hashes may be duplicates. The point of this is to flag fully duplicative groups ie a group were all records in that group are duplicates of all records in another group. The GUI needs all members of the group to have the same hash if it's to recognise them as a duplicate group.

I'm using STRING_AGG to concatenate the individual hashes of the records in the group, and sorting them by the hash to make sure I get the same string of characters for duplicate groups. I don't actually care what the order of the hashes is, so long as it's the same each time. When I run it as a SELECT query, it works fine and I can see identical strings for duplicate groups. When I take that same SELECT query and put it into an UPDATE query, the ordering seems to get lost.

SELECT query:

SELECT STRING_AGG([Hash],';') WITHIN GROUP (ORDER BY [Hash] ASC), 
    FROM HashTable
    GROUP BY [GroupIdentifier]

This gives the results (for an example pair of duplicate groups):

73F294873462B2BA0E930FD16DCCB7;90E749375DF806CB6E3F5CA48FFA38;E44256CE7CFCB971EB679BAC25A697
73F294873462B2BA0E930FD16DCCB7;90E749375DF806CB6E3F5CA48FFA38;E44256CE7CFCB971EB679BAC25A697

When I put that same code into the UPDATE query, it doesn't sort them correctly:

UPDATE HashTable
SET GroupHashList = c.HashList
FROM HashTable INNER JOIN (SELECT (STRING_AGG([Hash],';') WITHIN GROUP (ORDER BY [Hash] ASC)) AS [HashList],
    [GroupIdentifier]
    FROM HashTable
    GROUP BY [GroupIdentifier]) c
    ON c.[GroupIdentifier] = HashTable.[GroupIdentifier]

Results for the same two groups that gets written to the table:

73F294873462B2BA0E930FD16DCCB7;90E749375DF806CB6E3F5CA48FFA38;E44256CE7CFCB971EB679BAC25A697
73F294873462B2BA0E930FD16DCCB7;E44256CE7CFCB971EB679BAC25A697;90E749375DF806CB6E3F5CA48FFA38

This is driving me crazy - what am I missing?

ETA Yes, the hashes are ugly to read. Basically what I'm getting the first time is

Hash1; Hash2; Hash3
Hash1; Hash2; Hash3

But when it's in the UPDATE query, I get

Hash1; Hash2; Hash3
Hash1; Hash3; Hash2

The update query is actually sorted by record ID, although I don't know if that's coincidence. (https://dbfiddle.uk/CPG1-z2l)

I have a table that consists of a record ID, a group ID (linking 1 or more records into a group) and a hash value for each record.

CREATE TABLE HashTable(
    RecordID VARCHAR(255),
    GroupIdentifier VARCHAR(255),
    Hash VARCHAR (255),
    GroupHashList VARCHAR(4000)
)

(I know this isn't an efficient table, but it's just a temp table for the purposes of this example).

I want to generate a hash for each group, so I thought the simplest way would be to concatenate the hashes of each record in the group. RecordIDs are unique, but what those records relate to are not necessarily unique so the hashes may be duplicates. The point of this is to flag fully duplicate groups ie a group were all records in that group are duplicates of all records in another group. The GUI needs all members of the group to have the same hash if it's to recognise them as a duplicate group.

I'm using STRING_AGG to concatenate the individual hashes of the records in the group, and sorting them by the hash to make sure I get the same string of characters for duplicate groups. I don't actually care what the order of the hashes is, so long as it's the same each time. When I run it as a SELECT query, it works fine and I can see identical strings for duplicate groups. When I take that same SELECT query and put it into an UPDATE query, the ordering seems to get lost.

SELECT STRING_AGG([Hash],';') WITHIN GROUP (ORDER BY [Hash] ASC), 
    FROM HashTable
    GROUP BY [GroupIdentifier]

This gives the results (for an example pair of duplicate groups):

73F294873462B2BA0E930FD16DCCB7;90E749375DF806CB6E3F5CA48FFA38;E44256CE7CFCB971EB679BAC25A697
73F294873462B2BA0E930FD16DCCB7;90E749375DF806CB6E3F5CA48FFA38;E44256CE7CFCB971EB679BAC25A697

When I put that same code into the UPDATE query, it doesn't sort them correctly:

UPDATE HashTable
SET GroupHashList = c.HashList
FROM HashTable INNER JOIN (SELECT (STRING_AGG([Hash],';') WITHIN GROUP (ORDER BY [Hash] ASC)) AS [HashList],
    [GroupIdentifier]
    FROM HashTable
    GROUP BY [GroupIdentifier]) c
    ON c.[GroupIdentifier] = HashTable.[GroupIdentifier]

Results for the same two groups that gets written to the table:

73F294873462B2BA0E930FD16DCCB7;90E749375DF806CB6E3F5CA48FFA38;E44256CE7CFCB971EB679BAC25A697
73F294873462B2BA0E930FD16DCCB7;E44256CE7CFCB971EB679BAC25A697;90E749375DF806CB6E3F5CA48FFA38

What am I missing?

What I'm getting the first time is

Hash1; Hash2; Hash3
Hash1; Hash2; Hash3

But when it's in the UPDATE query, I get

Hash1; Hash2; Hash3
Hash1; Hash3; Hash2

The update query is sorted by record ID, although I don't know if that's coincidence. (https://dbfiddle.uk/CPG1-z2l)

Tweeted twitter.com/StackDBAs/status/1580619411555201025

occurred Oct 13, 2022 at 18:00

Became Hot Network Question

occurred Oct 13, 2022 at 17:44

Post Migrated Here from stackoverflow.com (revisions)

occurred Oct 13, 2022 at 15:10

Source Link

asked Oct 13, 2022 at 9:44

Rebecca

Loading

Stack Exchange Network

Return to Question