Nicolai Iversen Nicolai Iversen - 1 month ago 9
MySQL Question

subquery problems in MySQL when using row_number

Halo everyone.

I have a set of different customers with unique ID's, the amount they spend and the date of the purchase. I want to have a table with all the ID's and the date of their first order. For instance:
enter image description here

I use MySQL Workbench 6.3 CE and have tried the following code:

SELECT t.amount, t.debtor_id, t.created
FROM ( SELECT @row_number:=IF(@debtorid=t.debtor_id,@row_number+1,1) AS RowNumber,
t.debtor_id,
t.created,
t.amount,
@debtorid:=t.debtor_id
FROM capital.transaction t,
(SELECT@row_num:=1) x,
(SELECT @debtorid :='') y
order by t.debtor_id, t.created) subquery

JOIN capital.transaction_event te ON t.id=te.auth_entry_id
WHERE (fromstate='SPX_APPROVED') and webshop_id='3579' and t.amount>2000 and RowNumber=1


I keep getting the message: "Error code: 1054. Unknown column 't.amount' in field list. What am I doing wrong here?

Answer

If you call the dinamic table subquery then you should refer to the related selectedc columns with subquery and not t

SELECT subquery.amount, subquery.debtor_id, subquery.created  
FROM ( SELECT @row_number:=IF(@debtorid=t.debtor_id,@row_number+1,1) AS RowNumber, 
t.debtor_id,
t.created, 
t.amount, 
@debtorid:=t.debtor_id 
FROM capital.transaction t, 
       (SELECT@row_num:=1) x,  
       (SELECT @debtorid :='') y 
order by t.debtor_id, t.created) subquery 

JOIN capital.transaction_event te ON subquery.id=te.auth_entry_id  
WHERE (fromstate='SPX_APPROVED') and webshop_id='3579' and subquery.amount>2000 and RowNumber=1