Jump to content


Photo

select within select


  • Please log in to reply
1 reply to this topic

#1 decrease789

decrease789
  • New Members
  • Pip
  • Newbie
  • 1 posts

Posted 30 August 2006 - 09:20 PM

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
+----------------+------+
| name           | rank |
+----------------+------+
| Enfant, L'     |    1 |
| Grand bleu, Le |    2 |
| Magnolia       |    3 |
| Spider         |  999 |
| Rosetta        |    4 |
+----------------+------+
i want
+----------------+------+
| name           | rank |
+----------------+------+
| Magnolia       |    3 |
| Rosetta        |    4 |
+----------------+------+
im running MySQL 4.0.24. i want to perform this mysql query but it won't work
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;
i tried this as an alternative
SET @rank = 1 ; 
SELECT name, rank FROM movies WHERE (@rank=rank-1 OR rank = 1) AND @rank:=rank AND 3 <= rank;
but it returns an empty set???? however it works for less than rather than greater than
SET @rank = 1 ; 
SELECT name, rank FROM movies WHERE (@rank=rank-1 OR rank = 1) AND @rank:=rank AND 3 >= rank;


#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 31 August 2006 - 03:54 AM

The problem is using the same user variable twice in the same query -- you get funny things happening.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users