At 18:32 -0800 2/18/03, Jon Drukman wrote:
this query:If you use GROUP BY on a column or set of columns, then the output column list should contain only those columns,
select ep.product, s.id,s.type,s.headline,date_format(post_date,'%a, %m/%d/%y - %h:%m %p') date,user_level,s.ext_url from story s left join e_prod ep on ep.story=s.id where s.post_date > date_sub(now(), interval 14 day) and s.status = 9 order by post_date desc limit 10
returns the following rows:
+---------+---------+------+--------------------------------------+--------------------------+------------+---------+
| product | id | type | headline | date | user_level | ext_url |
+---------+---------+------+--------------------------------------+--------------------------+------------+---------+
| NULL | 6023331 | 3 | Test story with no products | Tue, 02/18/03 - 06:02 PM | NULL | |
| NULL | 2910975 | 14 | The Sims Movie 3 | Fri, 02/14/03 - 07:02 PM | NULL | NULL |
| NULL | 2910974 | 14 | The Sims Movie 2 | Fri, 02/14/03 - 07:02 PM | NULL | NULL |
| NULL | 2910973 | 14 | The Sims Movie 1 | Fri, 02/14/03 - 07:02 PM | 0 | NULL |
| 589451 | 2910965 | 14 | The Sims Movie 3 | Fri, 02/14/03 - 07:02 PM | 10 | NULL |
| 589451 | 2910964 | 14 | The Sims Movie 2 | Fri, 02/14/03 - 07:02 PM | 10 | NULL |
| 589451 | 2910963 | 14 | The Sims Movie 1 | Fri, 02/14/03 - 07:02 PM | 0 | NULL |
| 561594 | 2910962 | 10 | Lionheart screens | Fri, 02/14/03 - 07:02 PM | NULL | NULL |
| 197345 | 2910961 | 3 | Square to begin closed beta for FFXI | Fri, 02/14/03 - 06:02 PM | 0 | |
| 561656 | 2910958 | 5 | Fighter Maker 2 Review | Fri, 02/14/03 - 05:02 PM | NULL | NULL |
+---------+---------+------+--------------------------------------+--------------------------+------------+---------+
what i would like is to group together product & type, but stop grouping between rows where product and type are not identical. in other words, the data returned by the above would look like this:
+---------+---------+------+--------------------------------------+--------------------------+------------+---------+
| product | id | type | headline | date | user_level | ext_url |
+---------+---------+------+--------------------------------------+--------------------------+------------+---------+
| NULL | 6023331 | 3 | Test story with no products | Tue, 02/18/03 - 06:02 PM | NULL | |
| NULL | 2910975 | 14 | The Sims Movie 3 | Fri, 02/14/03 - 07:02 PM | NULL | NULL |
| 589451 | 2910965 | 14 | The Sims Movie 3 | Fri, 02/14/03 - 07:02 PM | 10 | NULL |
| 561594 | 2910962 | 10 | Lionheart screens | Fri, 02/14/03 - 07:02 PM | NULL | NULL |
| 197345 | 2910961 | 3 | Square to begin closed beta for FFXI | Fri, 02/14/03 - 06:02 PM | 0 | |
| 561656 | 2910958 | 5 | Fighter Maker 2 Review | Fri, 02/14/03 - 05:02 PM | NULL | NULL |
if i add GROUP BY product,type to the query, it folds together too much - so the first two rows listed above get turned into one.
is it possible with a straight query or do i have to do it programmatically?
plus (optionally) the values of aggregate functions computed for each group of rows. If the output column list
contains other columns, then the query no longer makes sense for GROUP BY.
Sounds like you'll need to do it programmatically. Your query violates the conditions just listed.
-jsd-
--------------------------------------------------------------------- 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
