MYSQL Using incremented column in case when statement-CodePudding

I am trying to create a column when selecting data from a table to auto_increment the row count and the best solution I have found is to use ROW_NUMBER() OVER ( order by Date DESC) AS row_ because it is consistent with the order clause and has proven to work in my situation.

However I can't use the new column row_ when creating stats in a case when statement.

For example, I have the following statement:

SELECT ROW_NUMBER() OVER ( order by Date Desc) AS row_
      ,CASE WHEN row_ = 1 then 8 else 0 end as stat1
      ,CASE WHEN row_ = 2 then 2 else 0 end as stat2
FROM tblr R
JOIN tblrn RR ON R.RUniqueID = RR.WRID 
WHERE WHID = @VHID AND RDate < @RD
ORDER BY RDate DESC;

However I get an error saying: Unknown column 'row_' in field list.

Im really looking for something where I can have a table (row_ being calculated by the ROW_NUMBER()... section):

row_	col1	col2
1	blah	blah
2	blah	blah
3	blah	blah

and be able to use row_ in a case when situation so create another column to look like:

row_	col1	col2	stat1	stat2
1	blah	blah	8	0
2	blah	blah	0	2
3	blah	blah	0	0

CodePudding user response：

You can directly use the ROW_NUMBER() function within you CASE clause Like below:

SELECT ROW_NUMBER() OVER ( order by Date Desc) AS row_,
CASE WHEN ROW_NUMBER() OVER ( order by Date Desc) = 1 THEN 8 ELSE 0 END AS stat1,
CASE WHEN ROW_NUMBER() OVER ( order by Date Desc) = 2 THEN 2 ELSE 0 END AS stat2
FROM tblr R
JOIN tblrn RR ON R.RUniqueID = RR.WRID 
WHERE WHID = @VHID AND RDate < @RD
ORDER BY RDate DESC;

DEMO