Taken from another post, the sql listed further below creates/uses the input table
ID ITEM
1 a
1 b
2 a
2 c
2 d
3 b
3 z


and with the SELECT statement returns
ID items
1 a, b
2 a, c, d
3 b, z

The items are sorted in alphab. order (using Min) but I HAVE A 3RD FIELD IN THE INPUT TABLE THAT ORDERS THE ITEMS SO INPUT TABLE NOW IS:
ID ITEM ORDER
1 a 32
1 b 15
2 a 32
2 c 11
2 d 45
3 b 15
3 z 99

SO THE OUTPUT TABLE LOOKS SHOULD BE (items with smaller Order no are added first):
ID items
1 b, a
2 c, a, d
3 b, z

ANY IDEAS on HOW TO CHANGE THE FOLLOWING SQL TO DO THAT?
I've tried ordering the input table on ID, ORDER fields and then using the SELECT statement listed below but it adds the items in alphab. order which I guess it should do since it's using Min. I also tried using the ORDER field in the joins instead of the ITEM field but still didn't work. ANY IDEAS???



CREATE TABLE dbo.table00 (
id INT NOT NULL
, item CHAR(1) NOT NULL
)

INSERT INTO dbo.table00 (id, item)
SELECT 1, 'a'
UNION ALL SELECT 1, 'b'
UNION ALL SELECT 2, 'a'
UNION ALL SELECT 2, 'c'
UNION ALL SELECT 2, 'd'
UNION ALL SELECT 3, 'b'
UNION ALL SELECT 3, 'z'

SELECT a.id, Min(a.item)
+ Coalesce(', ' + Min(b.item), '')
+ Coalesce(', ' + Min(c.item), '')
FROM dbo.table00 AS a
LEFT JOIN dbo.table00 AS b
ON (b.id = a.id
AND a.item < b.item)
LEFT JOIN dbo.table00 AS c
ON (c.id = a.id
AND b.item < c.item)
GROUP BY a.id
ORDER BY a.id