0

I want to assign a value to a user defined variable based on a comparison of that variable and a field within a row. (MySQL)

Example:

SELECT
    @var1:= IF(@var2 = table.value, @var1 + 1, 1)
    @var2:= table.value
FROM table

I don't seem to be able to do comparisons (=,>,<,etc) on variables within the select portion of the statement.

EDIT: Changed the pseudo code to make more sense with my application.

kevinbuiied
  • 101
  • 1
  • How can you tell whether the comparison is working? Either way, `@var1` will be set to `table.value`. – Barmar May 19 '22 at 23:58
  • I edited the pseudo code to be more in line with what I currently have. Thank you for point that out! – kevinbuiied May 20 '22 at 00:14
  • Looks like you're trying to calculate a rank with each group. If you're using MySQL 8 you can use the `RANK()` window function for this. If not, see https://stackoverflow.com/questions/34667070/update-a-mysql-table-with-record-rankings-within-groups – Barmar May 20 '22 at 00:20
  • Thank you! Your answer led me to Row_Number(). It is exactly what I need. – kevinbuiied May 20 '22 at 00:43

0 Answers0