decrease789

what i want to do is return a list of films which have consecutive ranks at and above a certain number (in this example 3) how the hell can i get select within select working??? it keeps giving me error 1064 so from table with [code]+----------------+------+ | name          | rank | +----------------+------+ | Enfant, L'    |    1 | | Grand bleu, Le |    2 | | Magnolia      |    3 | | Spider        |  999 | | Rosetta        |    4 | +----------------+------+[/code] i want[code]+----------------+------+ | name          | rank | +----------------+------+ | Magnolia      |    3 | | Rosetta        |    4 | +----------------+------+[/code] im running MySQL 4.0.24. i want to perform this mysql query but it won't work[code]SET @rank = 1 ; SELECT name, rank FROM ( SELECT name, rank FROM movies WHERE (@rank=rank-1 OR rank = 1) AND @rank:=rank ) WHERE 3 <= rank;[/code]i tried this as an alternative[code] SET @rank = 1 ; SELECT name, rank FROM movies WHERE (@rank=rank-1 OR rank = 1) AND @rank:=rank AND 3 <= rank;[/code]but it returns an empty set???? however it works for less than rather than greater than [code] SET @rank = 1 ; SELECT name, rank FROM movies WHERE (@rank=rank-1 OR rank = 1) AND @rank:=rank AND 3 >= rank;[/code]