I can't figure out how to Order by with string_agg

Question

I have this query (I am using SQL Server 2019) and is working fine (combining Dates and Notes into one column). However, the result I am looking for is to have the latest date show up first.

How can I achieve that from this query?

SELECT ID,            

(SELECT string_agg(concat(Date, ': ', Notes), CHAR(13) + CHAR(10) + CHAR(13) + CHAR (10)) as Expr1

    FROM(SELECT DISTINCT nd.Notes, nd.Date
    FROM dbo.ReleaseTrackerNotes AS nd 
    INNER JOIN dbo.ReleaseTracker AS ac4 ON ac4.ID = nd.ReleaseTrackerID
    WHERE (ac4.ID = ac.ID)) AS z_1) AS vNotes 

FROM dbo.ReleaseTracker AS ac

GROUP BY ID

I have tried the ORDER BY but is not working Here is my table:

CREATE TABLE [dbo].[ReleaseTrackerNotes](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [ReleaseTrackerID] [int] NULL,
    [AOC_ModelID] [int] NULL,
    [Date] [date] NULL,
    [Notes] [nvarchar](800) NULL,
 CONSTRAINT [PK_ReleaseTrackerNotes] PRIMARY KEY CLUSTERED

CREATE TABLE [dbo].[ReleaseTracker](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [AOC_ModelID] [int] NOT NULL,
    [MotherboardID] [int] NOT NULL,
    [StatusID] [int] NOT NULL,
    [TestCateoryID] [int] NULL,
    [TestTypeID] [int] NULL,
    [DateStarted] [date] NULL,
    [DateCompleted] [date] NULL,
    [LCS#/ORS#] [nvarchar](20) NULL,
    [ETCDate] [date] NULL,
    [CardsNeeded] [nvarchar](2) NULL,
 CONSTRAINT [PK_Compatibility] PRIMARY KEY CLUSTERED

Tim Biegeleisen · Accepted Answer · 2020-03-21T03:53:20.723

4

Use WITHIN GROUP (ORDER BY ...):

SELECT
    ID,            
    STRING_AGG(TRY_CONVERT(varchar, Date, 101) + ': ' + Notes +
               CHAR(13) + CHAR(10) + CHAR(13), CHAR(10))
        WITHIN GROUP (ORDER BY Date DESC) AS Expr1
FROM
(
    SELECT DISTINCT ac4.ID, nd.Notes, nd.Date
    FROM dbo.ReleaseTrackerNotes AS nd 
    INNER JOIN dbo.ReleaseTracker AS ac4
        ON ac4.ID = nd.ReleaseTrackerID
) AS vNotes
GROUP BY ID;

edited Mar 21 '20 at 03:53

answered Mar 21 '20 at 02:59

Tim Biegeleisen

502,043
27
286
360

That throws an error "Msg 174, Level 15, State 1, Line 3 The STRING_AGG function requires 2 argument(s). Msg 156, Level 15, State 1, Line 10 Incorrect syntax near the keyword 'AS'." – peka Mar 21 '20 at 03:07
Now I am getting Msg 156, Level 15, State 1, Line 12 Incorrect syntax near the keyword 'FROM'. – peka Mar 21 '20 at 03:16
It looks like your query had other issues, I was not looking for that, I have updated again. – Tim Biegeleisen Mar 21 '20 at 03:22
I have added my second table to my question that I am using to join with. It still didn't work with error Msg 402, Level 16, State 1, Line 3 The data types date and varchar are incompatible in the add operator. – peka Mar 21 '20 at 03:35
Use `TRY_CONVERT` on your `Date` to first convert it to text before concatenating. – Tim Biegeleisen Mar 21 '20 at 03:41
Now the query is good and it works, but it's in the same order as the original with the latest date being at the end which I would like to reverse (the latest date in the front) – peka Mar 21 '20 at 03:52
Then use: `WITHIN GROUP (ORDER BY Date DESC)` – Tim Biegeleisen Mar 21 '20 at 03:53
Perfect.... That worked as expected. Thank you very much for this. Much appreciated – peka Mar 21 '20 at 04:06
I apologize for so many iterations, I am on a cell phone right now. – Tim Biegeleisen Mar 21 '20 at 04:26

I can't figure out how to Order by with string_agg

1 Answers1

Linked

Related