MySQL Query Question

I have a table that stores historical statistics.  I'm using it to query timeline data.

+----------------------+------------+------+-----+---------------------+----------------+
| Field                | Type       | Null | Key | Default             | Extra          |
+----------------------+------------+------+-----+---------------------+----------------+
| id      | bigint(20) | NO   | PRI | NULL                | auto_increment |
| person_id            | bigint(20) | NO   | MUL |                     |                |
| time_hack            | datetime   | NO   |     | 0000-00-00 00:00:00 |                |
| statA       | int(4)     | YES  |     | NULL                |                |
| statB | int(4)     | YES  |     | NULL                |                |
| statC        | int(4)     | YES  |     | NULL                |                |
| statD | int(4)     | YES  |     | NULL                |                |
| statE | int(4)     | YES  |     | NULL                |                |
+----------------------+------------+------+-----+---------------------+----------------+

The query I'm using:

            SELECT * FROM
            `performance_history`
            WHERE
            `performance_history`.`time_hack` >= DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY);

The problem I'm finding multiple records per person per day.  It appears the table records were inputted hourly rather than daily.    So I want to modify my query so I only retrieve one record per person per day.
The following table shows 4 records for person #1 generated on SEP, 15 2006.    Aside from the date, all the data is the same so I only need one of these records for this day.


|              15 |        1 | 2006-09-15 01:46:58 |                        2 |            38 |                   77 |                  100 |
|              16 |        1 | 2006-09-15 02:46:58 |                        2 |            38 |                   77 |                  100 |
|              17 |        1 | 2006-09-15 03:46:58 |                        2 |            38 |                   77 |                  100 |
|              18 |        1 | 2006-09-15 04:46:58 |                        2 |            38 |                   77 |                  100 |              


How do I modify my query so I retrieve only 1 record per person, per day?
tmonteitAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Cornelia YoderArtistCommented:
           SELECT * FROM
            `performance_history`
            WHERE
            `performance_history`.`time_hack` >= DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY) GROUP BY person_id;
0
tmonteitAuthor Commented:
yodercm,

your recommendation only produces one record per person for the entire 30 day interval.  I need 1 record per day per person for the interval.  

Assume the table has these values.  The result should produce 2 records. One for the 15th, and another for the 16th.

|              15 |        1 | 2006-09-15 01:46:58 |                        2 |            38 |                   77 |                  100 |
|              16 |        1 | 2006-09-15 02:46:58 |                        2 |            38 |                   77 |                  100 |
|              17 |        1 | 2006-09-15 03:46:58 |                        2 |            38 |                   77 |                  100 |
|              18 |        1 | 2006-09-15 04:46:58 |                        2 |            38 |                   77 |                  100 |  
|              19 |        1 | 2006-09-16 01:46:58 |                        3 |            20 |                   74 |                  100 |
|              20 |        1 | 2006-09-16 02:46:58 |                        3 |            20 |                   74 |                  100 |
|              21 |        1 | 2006-09-16 03:46:58 |                        3 |            20 |                   74 |                  100 |
|              22 |        1 | 2006-09-16 04:46:58 |                        3 |            20 |                   74 |                  100 |  


How do I do that?

0
Raynard7Commented:
If you did,

SELECT ph.person_id, date(ph.`time_hack`), min(ph.id) mi
FROM
            `performance_history` ph
WHERE
            ph.`time_hack` >= DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY)
GROUP BY
            1, 2

it would give you the ids that you are after, if you did

select
     ph1.*
from
(
            SELECT ph.person_id, date(ph.`time_hack`), min(ph.id) mi
            FROM `performance_history` ph
            WHERE ph.`time_hack` >= DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY)
            GROUP BY 1,2
) x1 inner join `performance_history` ph1 on x1.mi = ph1.id

it would give you all the information, 1 per person per day
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

tmonteitAuthor Commented:
Raynard, can you explain that more?  MySql Beginner here... is there another table I'm supposed to create?
0
krisgrpCommented:
Hi,
I think u can use the group by cluase to get the resultant u want..
like this.
  SELECT person_id, date(`time_hack`)
            FROM `performance_history`
            WHERE `time_hack` >= DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY)
            GROUP BY person_id,`time_hack` order by id`;
0
Raynard7Commented:
hi, mine should be sufficient

they are all referring to the same table - just doing sub queries on it, (meaning a query within a query) and aliasing (calling a query by an easier to refer to name)

krisgrp - as you will notice that this is what my first query does, however this will not show all the records that the author wants to see - it will only show you the date and the person_id - if you want more you need an id corresponding to one of those records (using a min for example) then linking back onto the same table to pull the rows with that given id
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.