trying to run this sql query but i'm getting the error above:
DELETE FROM db_session
where time NOT IN (SELECT MAX(time) FROM db_session GROUP BY username)
The only database that causes this problem (as far as I know) is MySQL (and related databases). You can replace the logic with a
delete s from db_session s join (select username, max(time) as maxtime from db_session s2 group by username ) ss on s.username = ss.username and s.time < ss.maxtime;
An added bonus is that the logic is also correct. Your logic deletes any rows where the time is not the maximum time for any
username. However, if it could keep multiple rows for a user name if the different rows happen to be the maximum time for some other user.