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

Reply via email to