On Tue, 12 Apr 2005, Rich Carr wrote:
>Hi Dan,
> Thanks very much! First, I can't figure out how to reply to this so
>that it shows up in the MySQL list. How does one do it?
erm... if you hit 'reply all' or answer yes to 'reply to all' it should
send mail to [EMAIL PROTECTED]
Ahhh...I see what you mean now... you want to reply to *that* message in
the archive... Don't know.
> Second, when I tried to suggest I get the following error message, I'm
>using MySQL 4.0.15
ahhh... you need >= 4.1 to support subqueries...
I cc:'ed this back to the mailing list if anyone else can help.
Let me reformat your query to check
SELECT *
FROM viewvisitor
INNER JOIN ( select app, itemid, sum(viewcount) AS totalcount,
FROM viewvisitor
GROUP BY concat( app,itemid )
)
USING( app, itemid, visitorid, itemid, itemname,
vusername,totalcount, lastviewtime);
ERROR 1064: You have an error in your SQL syntax. Check the manual that
corresponds to your MySQL server version for the right syntax to use near
'select ...'
Yeah.. it don't like the sub-query.
Not sure what your query is actually doing, but it is academic unless you
upgrade your server.
Try this recipy...
create temporary table bleah
select
PK,
MIN(value_that_you_want_to_min_of_in_the_results_row)
as the_original_column name
from meTable;
select a.* from meTable a
inner join bleah
using(PK,the_original_column_name);
Their should be a unique minimum for the_original_column.
When I said before that 'you really need to know what you are doing' -
what I mean is, I am very bad at explaining. Sorry if the above looks like
garble (but I can't do better).
>Thanks!
>
>Dan Bolser <[EMAIL PROTECTED]> wrote:
>
>I think the suggestion posted here...
>
>http://lists.mysql.com/mysql/182424
>
>should get you going in the right direction.
>
>You really need to know what you are doing to know if it is giving you the
>correct answer or not.
>
>It would be cool if their was something like a GROUP_ROW(cols, expr) to do
>what you want explicity.
>
>SELECT * FROM chi;
>+----------+----+------+
>| child_id | id | type |
>+----------+----+------+
>| 1 | 1 | 0 |
>| 2 | 1 | 0 |
>| 3 | 1 | 1 |
>| 4 | 1 | 2 |
>| 5 | 2 | 2 |
>+----------+----+------+
>
>-- Totally fake and probably silly SQL...
>SELECT id, group_row(child_id,type,child_id=min(child_id))
>FROM chi2 GROUP BY id;
>
>+----------+----+------+
>| child_id | id | type |
>+----------+----+------+
>| 1 | 1 | 0 |
>| 5 | 2 | 2 |
>+----------+----+------+
>
>or GROUP_ROW(cols,sortby=)
>
>-- More sillyness
>SELECT id, group_row(child_id,type,sortby=rand())
>FROM chi2 GROUP BY id;
>
>Just an idea.
>
>Dan.
>
>On Mon, 11 Apr 2005, Rich Carr wrote:
>
>>Is there a way to set which rows values are used by the GROUP BY clause
>>for the fields that are not in the GROUP BY clause?
>> In this following select statement the group by and order work but the
>>value of the lastviewtime field is not the value of the most recent
>>datetime row. Is there any way to modify the select statement so that
>>the returned lastviewtime field will have the most recent datetime?
>>
>> select app, itemid, ownerid, visitorid, vusername, lastviewtime,
>>sum(viewcount) AS totalcount, itemname from viewvisitor where ownerid =
>>2 GROUP BY concat( app, itemid ) ORDER BY totalcount
>>
>>
>>create table viewvisitor
>>(
>> app char(15),
>> itemid INT UNSIGNED NOT NULL,
>> ownerid INT UNSIGNED NOT NULL,
>> ousername varchar(25),
>> visitorid INT UNSIGNED NOT NULL,
>> vusername varchar(25),
>> vfullname varchar(70),
>> lastviewtime DATETIME NOT NULL,
>> viewcount INT,
>> itemname VARCHAR(40),
>>
>>
>> PRIMARY KEY master(app, visitorid, itemid),
>> INDEX (ownerid),
>> INDEX (lastviewtime),
>> INDEX (viewcount)
>>);
>>
>>Thanks!!!
>>
>>Rich
>>
>>
>>
>>---------------------------------
>>Do you Yahoo!?
>> Yahoo! Small Business - Try our new resources site!
>
>
>
>---------------------------------
>Do you Yahoo!?
> Yahoo! Small Business - Try our new resources site!
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
