MySQL Select statement - iterations on one row

user_14260634 0
Viewed 29

The goal is to variate price over a large range of numbers to be able to plot a figure. My current query returns one row with name and a default price.

I tried adding an increment variable:

select 
name, price*row_number from 
(select 
name, 
price, 
@rownum := @rownum+1 as row_number
from portfolio)dt
cross join (select @rownum := 0) r

However this (obviously) still returns just one row, while output should be multiple iterations using different values for row_number.

mysql-variables select mysql
1 Answers

The iteration is returning 1 row for each row in portfolio because is cross joining with a table of only one row select @rownum := 0

If I understood the problem correctly, you are better off cross joining portfolio to itself (or even better to any table with the approximate number of rows equivalent to the number of variations you want), for example:

 set @rownumber=0;
    
    select 
    name, price * rownumber from 
    (select 
    name, 
    price, 
    @rownum := @rownum+1 as rownumber
    from portfolio)dt
    cross join (select * from portfolio) r

spoiler alert: the plot will be a boring straight line :)

user_29649630
Related