user6431631 user6431631 - 6 months ago 9
MySQL Question

MySQL: Query for most recent entires of distict fields set prior to specified date

Here's the scenario.

I have a table 'components':

| id | sys_id | name | serial_num | model_num | date |
+----+---------+-------+------------+-----------+------------+
| 4 | CZMIL01 | comp1 | 0101 | ABAB | 2010-02-25 |
| 3 | CZMIL01 | comp2 | 0011 | AABB | 2010-02-03 |
| 8 | CZMIL02 | comp2 | 0001 | AAAB | 2010-02-03 |
| 5 | CZMIL02 | comp1 | 0010 | AABA | 2010-01-14 |
| 6 | CZMIL02 | comp2 | 0011 | AABB | 2010-01-14 |
| 7 | CZMIL02 | comp3 | 0100 | ABAA | 2010-01-14 |
| 1 | CZMIL01 | comp1 | 0000 | AAAA | 2010-01-01 |
| 2 | CZMIL01 | comp2 | 0001 | AAAB | 2010-01-01 |
+----+---------+-------+------------+-----------+------------+


Where 'date' specifies when a component was put into a system 'sys_id'. Only one component of a given name is in a system at a time; so if two entries have the same component name, the one with the later date has replaced the one with the earlier date.

Desired Solution:

I need a query which tells me which components where in a given system on a given date.

For example, if I specified CZMIL01 and 2010-02-05, the result would be:

| id | sys_id | name | serial_num | model_num | date |
+----+---------+-------+------------+-----------+------------+
| 1 | CZMIL01 | comp1 | 0000 | AAAA | 2010-01-01 |
| 3 | CZMIL01 | comp2 | 0011 | AABB | 2010-02-03 |

Answer

Here is one method:

select c.*
from components c
where c.date = (select max(c2.date)
                from components c2
                where c2.sys_id = c.sys_id and
                      c2.serial_num = c.serial_num and
                      c2.date <= '2010-02-05'
               );

You can add a where condition for a particular sys_id in the outer where.