Completely flustered with a Latest Date from Subset problem.

[crimanysakes]

Someone should write a FAQ on how to grab various "latest dates" from MySQL; cuz I'm quite confused.

Not sure how much to include; but here' my query to get my first "subset" of data:

select caseResults.* from caseResults, runs, scriptResults where runs.runID = scriptResults.runID and runs.runID in (476,477,479,480) and scriptResults.scriptResultID = caseResults.scriptResultID order by caseResults.caseID

This will return; for example a dataset of:

+--------------+----------------+--------+--------------+-----------+---------------------+-------------------+-----------------+
| caseResultID | scriptResultID | caseID | caseStatusID | published | startedCase         | caseExecutionTime | caseRunComments |
+--------------+----------------+--------+--------------+-----------+---------------------+-------------------+-----------------+
|        30462 |           9913 |      6 |            1 |         0 | 2007-04-02 07:56:46 |                 3 | NULL            | 
|        30467 |           9913 |      7 |            1 |         0 | 2007-04-02 07:56:59 |                 2 | NULL            | 
|        30014 |           9773 |      8 |            1 |         0 | 2007-04-02 06:12:19 |                 9 | NULL            | 
|        30025 |           9776 |      8 |            1 |         0 | 2007-04-02 06:12:59 |                 7 | NULL            | 
|        30029 |           9777 |      8 |            1 |         0 | 2007-04-02 06:13:37 |                 7 | NULL            | 
|        30047 |           9783 |      8 |            1 |         0 | 2007-04-02 06:16:08 |                12 | NULL            | 
|        30050 |           9784 |      8 |            1 |         0 | 2007-04-02 06:16:51 |                11 | NULL            | 
|        30055 |           9785 |      8 |            1 |         0 | 2007-04-02 06:17:34 |                11 | NULL            | 
|        30017 |           9773 |      9 |            1 |         0 | 2007-04-02 06:12:29 |                24 | NULL            | 
|        30048 |           9783 |      9 |            1 |         0 | 2007-04-02 06:16:21 |                24 | NULL            | 
|        30023 |           9773 |     10 |            1 |         0 | 2007-04-02 06:12:53 |                 4 | NULL            | 
......snip....

All told, lets say 500 records (i won't include them all for brevities sake).

Now, for each unique caseID (6, 7, 8..., 9, etc.) i want to return the latest caseResultsID as dictated by the startedCase date. So for instance, in the above data, for caseID 8 i'd want to have it return 30055.

I have been wrestling with this for a few days now, trying various joins, or sub-selects, or whatnot. And a) i'm not sure i understand how to use joins properly when you are using so many tables in the first place, and b) when I finally got something I thought would work (and it did) it was PAINFULLY and exponentially slow.

I could also do it per caseID (ie; a independent query for each unique caseID) but that seems like a bad design, and it would shove much at the database.

I fully understand that the original sub-select is returning more info that I really need; i'm just did that (for now) so that I could determine if my selects where returning the rows I was expecting.

At this point, I'm not even sure what to attempt anymore. Any help would be a godsend.

---c

[code green]

You haven't shown your tables design. So your query is hard to follow. I will make a stab and suggest you should have at least three tables. One to list the cases with IDs. One for the results with IDs. And the third correlates the IDs. This is a many to many design. It sounds like you are trying to do it with two tables.