decrease789 Posted August 30, 2006 Share Posted August 30, 2006 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] Link to comment https://forums.phpfreaks.com/topic/19207-select-within-select/ Share on other sites More sharing options...
fenway Posted August 31, 2006 Share Posted August 31, 2006 The problem is using the same user variable twice in the same query -- you get funny things happening. Link to comment https://forums.phpfreaks.com/topic/19207-select-within-select/#findComment-83263 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.