MySQL ODBC Connector - Help with query

Author

Help with query

Jay Paulson

2006-01-26, 4:56 pm

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 |
+-----------+------------+----------+-------+

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

SGreen@unimin.com

2006-01-26, 4:56 pm

--=_alternative 00546A0C85257101_=
Content-Type: text/plain; charset="US-ASCII"

Jay Paulson <Jay.Paulson@wholefoods.com> 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

--=_alternative 00546A0C85257101_=--

Sponsored Links