Require help to formulate a query.
I have the following table named `test_trace_information`:
+----------+------------+--------------+--------------------------------+
| trace_id | cal_date | cal_due_date | test_instruments_instrument_id |
+----------+------------+--------------+--------------------------------+
| 1 | 2008-12-02 | 2009-12-02 | 3 |
| 2 | 2009-04-21 | 2010-04-21 | 34 |
| 3 | 2009-11-21 | 2010-11-21 | 3 |
| 4 | 2010-04-04 | 2011-04-04 | 34 |
+----------+------------+--------------+--------------------------------+
This table contains the calibration date and due date for given test instruments. Whenever an instrument is calibrated, a new entry is inserted into this table. One instrument can have multiple entries.
I would like to do a query on this table that returns rows where there is one row for each `test_instruments_instrument_id` where the `test_instruments_instrument_id` has the greatest `cal_due_date`.
So given the above table, the results would be the following:
+----------+------------+--------------+--------------------------------+
| trace_id | cal_date | cal_due_date | test_instruments_instrument_id |
+----------+------------+--------------+--------------------------------+
| 3 | 2009-11-21 | 2010-11-21 | 3 |
| 4 | 2010-04-04 | 2011-04-04 | 34 |
+----------+------------+--------------+--------------------------------+
What SELECT should I use?
Thanks for your help.