I see no column named Site.nome showing that it is wrong. ds wrote:
>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> > --------------------------------------------------------------------- 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
