Search code examples
mysqlsqlmysql-error-1111

mySQL query error 'Invalid use of group function'

I have set up a price comparison tool that crawls websites and then detects price changes. I have a table that contains the price, the items id and the scan id. I have another table which contains the item info and another table which contains a list of scans and when they took place.

I am trying to make a query that can detect new products and price changes. This is what I have... but it causes an error 'Invalid use of group function'

SELECT * FROM 
(SELECT * FROM price WHERE scan_id = MAX(scan_id) - 1) as P 

LEFT JOIN 

(SELECT * FROM price WHERE scan_id = MAX(scan_id)) as N 

ON 
P.item_id = N.item_id 
AND P.price != N.price 

JOIN item I ON N.item_id = I.item_id

For some reason this query is not working.

Solution

  • The problem was with the MAX() part of the sub query. I removed it and replaced it with the numbers and it worked a treat.