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>
>
