Benching
Somebody knows why 4.1 is faster than 5.0 mysql versions
Ing. Jaime Fuentes R.
997500459
2421905-2423252
Enviado desde mi BlackBerry de Claro.
-----Original Message-----
From: Brent Baisley <brenttech@gmail.com>
Date: Tue, 2 Jun 2009 12:32:39
To: Ray<ray@stilltech.net>
Cc: <mysql@stripped>
Subject: Re: Question about query - can this be done?
On Tue, Jun 2, 2009 at 11:52 AM, Ray <ray@stripped> wrote:
> Hello,
>
> I've tried the manual and google, but I am not even sure what to call what I
> want to do.
>
> simplified data example:
> I have a table of start and end times for an event, and an id for that event
> in a table. each event may occur multiple times, but never more than 5 times
> and rarely more than 3.
> I want a query that will provide one record per event with all times included.
> feel free to answer RTFM or STFW as long as you provide the manual section or
> key words. ;)
> Thanks,
> Ray
>
>
> chart form follows:
>
> id | event_id | start | end
> ---------------------------------------
> 1 | 4 | t1 | t2
> 2 | 4 | t3 | t4
> 3 | 4 | t5 | t6
> 4 | 5 | t1 | t2
> 5 | 5 | t3 | t4
>
> becomes
>
> id | event_id | start | end | start | end | start | end
> ---------------------------------------------------------------------------
> ? | 4 | t1 | t2 | t3 | t4 |
t5 | t6
> ? | 5 | t1 | t2 | t3
| t4
>
I think what you are looking for is GROUP_CONCAT. You can just GROUP
BY event id, and then process the resulting delimited string on the
front end.
SELECT event_id, GROUP_CONCAT(start) start_dates, GROUP_CONCAT(end) end_dates
FROM events GROUP BY event_id
Or even combined start and end dates into a single string and group them.
SELECT event_id, GROUP_CONCAT( CONCAT(start, '-', end) ) start_end
FROM events GROUP BY event_id
But, if you really want to get it in the column format you indicate,
you can make a much more complicated query. Use SUBSTRING_INDEX to
split out the parts of the group you need.
SELECT event_id,
SUBSTRING_INDEX(GROUP_CONCAT(start), ',', 1 ) start1,
SUBSTRING_INDEX(GROUP_CONCAT(end), ',', 1 ) end1,
SUBSTRING_INDEX( SUBSTRING_INDEX(GROUP_CONCAT(start), ',', 2 ), ',',
-1 ) start2,
SUBSTRING_INDEX( SUBSTRING_INDEX(GROUP_CONCAT(end), ',', 2 ), ',', -1 ) end2,
SUBSTRING_INDEX( SUBSTRING_INDEX(GROUP_CONCAT(start), ',', 3 ), ',',
-1 ) start3,
SUBSTRING_INDEX( SUBSTRING_INDEX(GROUP_CONCAT(end), ',', 3 ), ',', -1 ) end3,
SUBSTRING_INDEX( SUBSTRING_INDEX(GROUP_CONCAT(start), ',', 4 ), ',',
-1 ) start4,
SUBSTRING_INDEX( SUBSTRING_INDEX(GROUP_CONCAT(end), ',', 4 ), ',', -1 ) end4,
SUBSTRING_INDEX( SUBSTRING_INDEX(GROUP_CONCAT(start), ',', 5 ), ',',
-1 ) start5,
SUBSTRING_INDEX( SUBSTRING_INDEX(GROUP_CONCAT(end), ',', 5 ), ',', -1 ) end5
FROM events GROUP BY event_id;
I think that will give the format you specified, but I am not
recommending you do it this way.
Hope that helps.
Brent Baisley
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=1sat.com
