Hi, I'm having a strange problem using SQL vars (@variable) and an order by expression.
I have a query that returns a record for each day. Each record has a date (data), a column with the daily total value (uv_views) and other with the accumulated value until the current day (acc_views). The other columns are IDs. The problem is that if i only order by date, the results are ok... If i order by "date,Site.nome" (you would say Site.name) the results are wrong and i don't know where it got those results. Here is the output. I have limited the values to 4 (it only returns rows with 0 but it doesn't matter). In the second "big" (not so big) query, look at "acc_views" accumulated value... How can it be ?!?! Thank you in advance for trying to help. mysql> select @diario_views:=0,@acc_views:=0; +------------------+---------------+ | @diario_views:=0 | @acc_views:=0 | +------------------+---------------+ | 0 | 0 | +------------------+---------------+ 1 row in set (0.00 sec) mysql> SELECT data, @diario_views:=uv_views as views, @acc_views:=@acc_views+@diario_views as acc_views, secloc.site_id,secloc.section_id,secloc.location_id FROM secloc,Site,Section,Location WHERE secloc.campaign_id='9' and secloc.site_id=Site.site_id and secloc.section_id=Section.section_id and secloc.location_id=Location.location_id ORDER BY data LIMIT 4; +------------+-------+-----------+---------+------------+-------------+ | data | views | acc_views | site_id | section_id | location_id | +------------+-------+-----------+---------+------------+-------------+ | 2001-09-20 | 0 | 0 | 2 | 1 | 4 | | 2001-09-20 | 0 | 0 | 3 | 1 | 4 | | 2001-09-20 | 0 | 0 | 4 | 1 | 4 | | 2001-09-20 | 0 | 0 | 6 | 1 | 4 | +------------+-------+-----------+---------+------------+-------------+ 4 rows in set (0.02 sec) mysql> select @diario_views:=0,@acc_views:=0; +------------------+---------------+ | @diario_views:=0 | @acc_views:=0 | +------------------+---------------+ | 0 | 0 | +------------------+---------------+ 1 row in set (0.00 sec) mysql> SELECT data, @diario_views:=uv_views as views, @acc_views:=@acc_views+@diario_views as acc_views, secloc.site_id,secloc.section_id,secloc.location_id FROM secloc,Site,Section,Location WHERE secloc.campaign_id='9' and secloc.site_id=Site.site_id and secloc.section_id=Section.section_id and secloc.location_id=Location.location_id ORDER BY data,Site.nome LIMIT 4; +------------+-------+-----------+---------+------------+-------------+ | data | views | acc_views | site_id | section_id | location_id | +------------+-------+-----------+---------+------------+-------------+ | 2001-09-20 | 0 | 227815 | 3 | 1 | 4 | | 2001-09-20 | 0 | 263822 | 4 | 1 | 4 | | 2001-09-20 | 0 | 0 | 2 | 1 | 4 | | 2001-09-20 | 0 | 331278 | 6 | 1 | 4 | +------------+-------+-----------+---------+------------+-------------+ 4 rows in set (0.04 sec) mysql> -- dsoares (sql) --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
