Assigning multiple variables in one select-CodePudding

I have the following query:

SELECT
    ROW_NUMBER() OVER(ORDER BY A.Price) AS R,
    A.Price
FROM 
    TableA A
INNER JOIN 
    TableB B ON A.ID = B.ID
INNER JOIN 
    TableC C ON C.Code = A.Code
WHERE
    C.Type = 125
    AND A.Desc = 10000038
    AND C.YearID = 10000021
GROUP BY 
    A.Price

This query returns

R	Price
1	165
2	487
3	1807

Is it possible assigning variables @a = 165, @b = 487, and @c = 1807 in one select?

I wrote this query:

WITH P AS (
SELECT
    ROW_NUMBER() OVER(ORDER BY A.Price) AS R,
    A.Price
FROM 
    TableA A
INNER JOIN 
    TableB B ON A.ID = B.ID
INNER JOIN 
    TableC C ON C.Code = A.Code
WHERE
    C.Type = 125
    AND A.Desc = 10000038
    AND C.YearID = 10000021
GROUP BY 
    A.Price
)
SELECT
    @a = (CASE WHEN [R] = 1 THEN P.[Price] END),
    @b = (CASE WHEN [R] = 2 THEN P.[Price] END),
    @c = (CASE WHEN [R] = 3 THEN P.[Price] END)
FROM P;

But only @c gets the value.

CodePudding user response：

You need to aggregate. Your query, as it stands, returns 3 rows, which means that the variables are assigned values 3 times as well. What order this is in is arbitrary too, but it might be that @a is first assigned the value 165, then NULL, and then NULL again.

If you aggregate, you can return one row and then the assignments will work as you expect; with each variable being assigned 1 value (not 3):

SELECT @a = MAX(CASE WHEN [R] = 1 THEN P.[Price] END),
       @b = MAX(CASE WHEN [R] = 2 THEN P.[Price] END),
       @c = MAX(CASE WHEN [R] = 3 THEN P.[Price] END)
FROM P;