How can I order manipulated data that I store in a variable in SQL?-CodePudding

I have a variable @columns that outputs a set of decimals. For example, [0.00000], [1.00000], [4.00000], [3.00000]. These later get used as columns in one of my queries. However the decimals don't get put in any particular order. I would like to order them from least to greatest in SQL. I don't see a function that does this.

How can I manipulate the data to do that?

SELECT
    @COLUMNS  = QUOTENAME(ct.rate)   ','
FROM
(
SELECT DISTINCT ct.rate FROM
DB.table ct
WHERE ct.ID = @ID
) ct

SET @COLUMNS = LEFT(@COLUMNS, LEN(@COLUMNS) - 1);

PRINT @COLUMNS```

CodePudding user response：

If you order the data before the array, you will get the output ordered from least to greatest. As you can't use order inside subquery, you can select a TOP MAX(INT) to subquery it.

SELECT
    @COLUMNS  = QUOTENAME(ct1.rate)   ','
FROM
(
SELECT DISTINCT  TOP 2147483647 ct.rate FROM
DB.table ct
WHERE ct.ID = @ID
ORDER BY ct.rate
) ct1