tmonteit
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`.`tim e_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?
+----------------------+--
| 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`.`tim
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Raynard, can you explain that more? MySql Beginner here... is there another table I'm supposed to create?
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`;
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`;
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
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
ASKER
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?