SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Member
    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 |       |
    +--------------------+--------------+------+-----+---------------------+-------+
    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:

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

  2. #2
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    695
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •