GROUP BY is too greedy

[Jon Drukman]

this query:

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?

-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