Jay Paulson <[EMAIL PROTECTED]> wrote on 01/25/2006 10:09:36 AM: > From the result set below I have 22 rows and the only difference is the > date. I was wondering if there was a way to get all of these results using > GROUP BY instead of having to use LIMIT?? > > As this table grows I'm going to want to get a LIMIT 0,77 but would like it > to be grouped by date. So basically I want 7 groups of results and in each > group there should be 11 rows. > > Any idea how to do this? Should it be separate queries or should I just > break down and use LIMIT? > > thanks > > My attempt was, but doesn't really work. > > SELECT region_id, date, page_hit, score > FROM statistics > WHERE date <= '2006-01-29' > GROUP BY region_id, date > ORDER BY date DESC; > > +-----------+------------+----------+-------+ > | region_id | date | page_hit | score | > +-----------+------------+----------+-------+ > | CE | 2006-01-23 | 978 | 6.72 | > | FL | 2006-01-23 | 558 | 2.75 | > | MA | 2006-01-23 | 312 | 0.09 | > | MW | 2006-01-23 | 478 | 0.25 | > | NA | 2006-01-23 | 4846 | 4.85 | > | NC | 2006-01-23 | 3281 | 3.03 | > | PN | 2006-01-23 | 3281 | 1.22 | > | SW | 2006-01-23 | 1964 | 1.05 | > | RM | 2006-01-23 | 1964 | 2.80 | > | SO | 2006-01-23 | 173 | 0.11 | > | SP | 2006-01-23 | 163 | 0.07 | > | CE | 2006-01-29 | 978 | 6.72 | > | FL | 2006-01-29 | 558 | 2.75 | > | MA | 2006-01-29 | 312 | 0.09 | > | MW | 2006-01-29 | 478 | 0.25 | > | NA | 2006-01-29 | 4846 | 4.85 | > | NC | 2006-01-29 | 3281 | 3.03 | > | PN | 2006-01-29 | 3281 | 1.22 | > | SW | 2006-01-29 | 1964 | 1.05 | > | RM | 2006-01-29 | 1964 | 2.80 | > | SO | 2006-01-29 | 173 | 0.11 | > | SP | 2006-01-29 | 163 | 0.07 | > +-----------+------------+----------+-------+ >
Can you please explain what you mean by "7 groups of results and in each group there should be 11 rows". Eleven times seven is seventy-seven, the number of rows you already plan to be getting. I see two groups of results, each group consisting of 7 rows. What would you rather the output look like other than what you showed us? If you want seven separate sets of results, you have to run seven separate queries. All databases work that way. Maybe if you described your problem and your data and your data structures in greater detail, one of us may have a solution different than the one you are currently working on. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
