Where to use WITHIN GROUP function without STRING_AGG function

Question

see this code

https://stackoverflow.com/questions/194852/how-to-concatenate-text-from-multiple-rows-into-a-single-text-string-in-sql-serv?rq=1

SELECT GroupName, STRING_AGG(Name, ', ') WITHIN GROUP (ORDER BY Name ASC) AS Departments
FROM HumanResources.Department 
GROUP BY GroupName;

1) What WITHIN GROUP is doing in above sql ? WITHIN GROUP used to show data in specific order ?

thanks

Answer 1

Hi @Sudip Bhatt ，

In STRING_AGG,optionally specify order of concatenated results using WITHIN GROUP clause.
Please refer to an example：

    USE AdventureWorks2017  
    GO  
      
    SELECT TOP 10 City, STRING_AGG(CONVERT(NVARCHAR(max), EmailAddress), ';') WITHIN GROUP (ORDER BY EmailAddress ASC) AS emails   
    FROM Person.BusinessEntityAddress AS BEA    
    INNER JOIN Person.Address AS A ON BEA.AddressID = A.AddressID  
    INNER JOIN Person.EmailAddress AS EA ON BEA.BusinessEntityID = EA.BusinessEntityID   
    GROUP BY City  
    ORDER BY City;  
      
    SELECT TOP 10 City, STRING_AGG(CONVERT(NVARCHAR(max), EmailAddress), ';') AS emails   
    FROM Person.BusinessEntityAddress AS BEA    
    INNER JOIN Person.Address AS A ON BEA.AddressID = A.AddressID  
    INNER JOIN Person.EmailAddress AS EA ON BEA.BusinessEntityID = EA.BusinessEntityID   
    GROUP BY City  
    ORDER BY City;  

Output:

The WITHIN GROUP clause is specified in the first query, so EmailAddress is separated by ‘;’ in alphabetical order and merged in one line.
The WITHIN GROUP clause is omitted in the second query, so the out-of-order EmailAddress is separated by ‘;’ and merged in one line.

If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

WITHIN GROUP is used to specify the order that the items in STRING_AGG will appear. For example, in your query if the GroupName "Headquarters" had three departments, "Sales", Marketing", and "Finance" the result would be "Finance, Marketing, Sales".

If you don't use the WITHIN GROUP clause, then the order of the items is undetermined.

Tom

Answer 3

WITHIN GROUP is a clause used with some aggregate and analytic function where you want an order-related result. For string_agg you use it to mandate that the list is sorted in a certain order. (And I am quite sure that I have explained this before.)

WITHIN GROUP can also be used with the functions PERCENTILE_DISC and PERCENTILE_CONT. In fact, with these functions, the clause is mandatory.

... and before you ask what these functions do, please review your old threads. I am quite sure that I have explained these to you before when you were asking about median.