Order by before using in For XML Path with GROUP BY?-CodePudding

CREATE TABLE #AD 
(
    [PART NUMBER] nvarchar(255), 
    [PLANT] nvarchar(255), 
    [VENDOR CODE] nvarchar(255), 
    [SUPPLIER NAME] nvarchar(255), 
    [POSTING DATE] int
)

INSERT INTO #AD 
VALUES ('123', '1001', '1', 'VEND1', 20220304),
       ('123', '1001', '2', 'VEND2', 20220611)

SELECT 
    AD.*,
    REPLACE(STUFF((SELECT (', ('   AD2.[VENDOR CODE]   ') '   AD2.[SUPPLIER NAME])
                   FROM #AD AD2
                   WHERE AD.[PART NUMBER] = AD2.[PART NUMBER] 
                     AND AD.PLANT = AD2.PLANT 
                     AND (AD2.[POSTING DATE] BETWEEN 20220101 AND 20221231)-- Is there a way to order this by POSTING DATE DESC within here?
                   GROUP BY AD2.[VENDOR CODE], AD2.[SUPPLIER NAME] 
                   FOR XML PATH('')), 1, 2, ''), '&amp;', '&') AS [Supplier]
FROM  
    #AD AD

Desired Output:

PART NUMBER	PLANT	VENDOR CODE	SUPPLIER NAME	POSTING DATE	Supplier
123	1001	1	VEND1	20220304	(2) VEND2, (1) VEND1
123	1001	2	VEND2	20220611	(2) VEND2, (1) VEND1

I want to order the suppliers column to have the most recent supplier first (most recent posting date, so should list Vend2 first in this case). I tried just adding an ORDER BY [POSTING DATE] DESC right before FOR XML PATH, but I can't do that because I don't want to add it to the select statement or group by.

I also tried doing a subquery of just selecting everything ordering by posting date but you cannot order by within a subquery.

Any thoughts on accomplishing this?

CodePudding user response：

You can still order the dates, only you need an aggregation function.

I used ORDER BY MAX(AD2.[POSTING DATE]) DESC that should bring the right order

Still as I posted in my comment, an update to something more recent, brings some benefits including STRING_AGG

CREATE TABLE #AD 
(
    [PART NUMBER] nvarchar(255), 
    [PLANT] nvarchar(255), 
    [VENDOR CODE] nvarchar(255), 
    [SUPPLIER NAME] nvarchar(255), 
    [POSTING DATE] int
)

INSERT INTO #AD 
VALUES ('123', '1001', '1', 'VEND1', 20220304),
       ('123', '1001', '2', 'VEND2', 20220611)

SELECT 
    AD.*,
    REPLACE(STUFF((SELECT (', ('   AD2.[VENDOR CODE]   ') '   AD2.[SUPPLIER NAME])
                   FROM #AD AD2
                   WHERE AD.[PART NUMBER] = AD2.[PART NUMBER] 
                     AND AD.PLANT = AD2.PLANT 
                     AND (AD2.[POSTING DATE] BETWEEN 20220101 AND 20221231)-- Is there a way to order this by POSTING DATE DESC within here?
                   GROUP BY AD2.[VENDOR CODE], AD2.[SUPPLIER NAME] 
  ORDER BY MAX(AD2.[POSTING DATE]) DESC
                   FOR XML PATH('') ), 1, 2, '')
  , '&', '&') AS [Supplier]
FROM  
    #AD AD

PART NUMBER	PLANT	VENDOR CODE	SUPPLIER NAME	POSTING DATE	Supplier
123	1001	1	VEND1	20220304	(2) VEND2, (1) VEND1
123	1001	2	VEND2	20220611	(2) VEND2, (1) VEND1

fiddle

CodePudding user response：

How about the following solution?

SQL

DECLARE @tbl TABLE 
(
    [PART NUMBER] nvarchar(255), 
    [PLANT] nvarchar(255), 
    [VENDOR CODE] nvarchar(255), 
    [SUPPLIER NAME] nvarchar(255), 
    [POSTING DATE] int
);
INSERT INTO @tbl VALUES 
('123', '1001', '1', 'VEND1', 20220304),
('123', '1001', '2', 'VEND2', 20220611);

SELECT * FROM @tbl;

SELECT AD.[PART NUMBER], ad.PLANT
    , REPLACE(STUFF((SELECT (', ('   AD2.[VENDOR CODE]   ') '   AD2.[SUPPLIER NAME])
                   FROM @tbl AD2
                   WHERE AD.[PART NUMBER] = AD2.[PART NUMBER] 
                     AND AD.PLANT = AD2.PLANT 
                     AND (AD2.[POSTING DATE] BETWEEN 20220101 AND 20221231)-- Is there a way to order this by POSTING DATE DESC within here?
                   ORDER BY AD2.[POSTING DATE] DESC
                   FOR XML PATH('')), 1, 2, ''), '&amp;', '&') AS [Supplier]
FROM @tbl AD
GROUP BY AD.[PART NUMBER], ad.PLANT;

Output

PART NUMBER	PLANT	Supplier
123	1001	(2) VEND2, (1) VEND1