SitePoint Sponsor |
|
User Tag List
Results 1 to 2 of 2
-
Mar 7, 2005, 08:28 #1
- Join Date
- Mar 2005
- Posts
- 3
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Limiting Results to First Returned Row with Distinct field
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:
Code:+--------------------+--------------+------+-----+---------------------+-------+ | 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 | | +--------------------+--------------+------+-----+---------------------+-------+
Code: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);
Does anyone have any suggestions
-
Mar 7, 2005, 10:27 #2
- Join Date
- Jan 2004
- Location
- Uppsala, sverige
- Posts
- 700
- Mentioned
- 2 Post(s)
- Tagged
- 1 Thread(s)
Code: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
Bookmarks