homepage Welcome to WebmasterWorld Guest from
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

MySQL query with a variable which results randomly change
Problem with a particular query

5+ Year Member

Msg#: 4386653 posted 11:33 am on Nov 14, 2011 (gmt 0)


I have a query that seems it changes its result randomly. I supose that I'm doing something wrong but when I review my query I don't find it. I hope you could help me.

The query orders the results and I use a variable for getting the row position:

SET @row_number_old :=0;
SELECT p.id, p.name, f.id, f.others, s.value, s.old_value, (
@row_number_old := @row_number_old +1
) AS old_position
FROM profile p
INNER JOIN other_table f ON f.profile_id = p.id
INNER JOIN score s ON s.profile_id = p.id
ORDER BY old_value DESC

Despite the data stored in the DB is always the same, the problem is that 'old_position' value is not always the same in the results. Sometimes it has the correct value (the row position), but another times it has the value of f.id inexplicably.



WebmasterWorld Administrator coopster us a WebmasterWorld Top Contributor of All Time 10+ Year Member

Msg#: 4386653 posted 7:29 pm on Jan 2, 2012 (gmt 0)

Is it because of your ORDER BY? It seems that could be the issue. Also, although you may be OK in this query, for future reference you may want to be careful reading and setting in the same SELECT query:

While it is also possible both to set and to read the value of the same variable in a single SQL statement using the := operator, this is not recommended. Section 8.4, “User-Defined Variables”, explains why you should avoid doing this.


Global Options:
 top home search open messages active posts  

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved