MySQL Forums
Forum List  »  Newbie

Require help to formulate a query.
Posted by: Steve Shantz
Date: November 11, 2010 07:37AM

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.

Options: ReplyQuote


Subject
Written By
Posted
Require help to formulate a query.
November 11, 2010 07:37AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.