MySQL Forums :: Newbie :: Require help to formulate a query.


Advanced Search

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. Steve Shantz 11/11/2010 07:37AM
Re: Require help to formulate a query. laptop alias 11/11/2010 07:43AM
Re: Require help to formulate a query. Rick James 11/11/2010 11:59PM
Re: Require help to formulate a query. Steve Shantz 11/12/2010 08:34AM
Re: Require help to formulate a query. laptop alias 11/12/2010 08:38AM


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.