List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:February 19 2003 2:53am
Subject:Re: GROUP BY is too greedy
View as plain text  
At 18:32 -0800 2/18/03, Jon Drukman wrote:
>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?

If you use GROUP BY on a column or set of columns, then the output 
column list should contain only those columns,
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-

Thread
GROUP BY is too greedyJon Drukman19 Feb
  • Re: GROUP BY is too greedyPaul DuBois19 Feb