asked on

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?

SOLUTION

Cornelia Yoder

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

tmonteit

ASKER

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?

ASKER CERTIFIED SOLUTION

Raynard7

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

tmonteit

ASKER

Raynard, can you explain that more? MySql Beginner here... is there another table I'm supposed to create?

krisgrp

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`;

Raynard7

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