Jump to content

Archived

This topic is now archived and is closed to further replies.

decrease789

select within select

Recommended Posts

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]

Share this post


Link to post
Share on other sites
The problem is using the same user variable twice in the same query -- you get funny things happening.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.