Limiting Results to First Returned Row with Distinct field

[utklwg]

Hi All,

I'm a relatively new MySQL user and was wondering if someone could help me with a query that I'm stuck on. My boss asked me to build a simple inventory system. The system has a table called consumables_activity that looks like the following:

+--------------------+--------------+------+-----+---------------------+-------+
| Field              | Type         | Null | Key | Default             | Extra |
+--------------------+--------------+------+-----+---------------------+-------+
| id                 | int(11)      |      |     | 0                   |       |
| part_num           | varchar(15)  |      |     |                     |       |
| quantity_change    | int(11)      |      |     | 0                   |       |
| activity_date      | date         |      |     | 0000-00-00          |       |
| job_description    | text         |      |     |                     |       |
| remaining_quantity | int(11)      |      |     | 0                   |       |
| activity_type      | int(11)      |      |     | 0                   |       |
| users_name         | varchar(30)  |      |     |                     |       |
| cost_per_unit      | decimal(7,2) |      |     | 0.00                |       |
| username           | varchar(15)  |      |     |                     |       |
| timestamp          | timestamp    | YES  |     | 0000-00-00 00:00:00 |       |
+--------------------+--------------+------+-----+---------------------+-------+

I'm wanting to build a select statement that selects the first row with the most recent date per for each unique part_num. My initial hunch was to do something like the following:

SELECT (remaining_quantity+quantity_change) AS quantity, part_num,
activity_date FROM consumables_activity WHERE id IN (SELECT id FROM 
consumables_activity GROUP BY activity_date ASC part_num having count(*) > 0 
ORDER BY activity_date ASC);

While the query does seem to get the distinct part_nums, it isn't return the most recent activity_date.

Does anyone have any suggestions

[swampBoogie]

SELECT (remaining_quantity+quantity_change) AS quantity, 
part_num,
activity_date 
FROM consumables_activity  ca
WHERE activity_date IN (
  SELECT max(activity_date) 
 from consumables_activity 
  part_num = ca.part_num)
ORDER BY activity_date ASC