I am using FlySpeed SQL Query to get data from Bugzilla/Testopia, which is a Mozilla bug/testcase tracking system.

I have a query that works ... it grabs all of the test cases in a particular test run. The database actually has a record for EACH change to a particular test case. In other words, for testcase 12345, it has one record for IDLE, another record for FAILED and another record for PASSED and it adds these each time you update the testcase. This is a good thing because you can get the entire history of your testcase, but I only want to get the LAST entry.

This is my query:

Select
test_runs.plan_id,
test_case_runs****n_id,
test_case_runs.case_run_status_id,
test_case_runs.case_id,
test_runs.summary,
test_case_run_status.name
From
test_plans Inner Join
test_runs On test_plans.plan_id = test_runs.plan_id Inner Join
test_case_runs On test_runs****n_id = test_case_runs****n_id Inner Join
test_case_run_status On test_case_runs.case_run_status_id =
test_case_run_status.case_run_status_id Inner Join
test_cases On test_case_runs.case_id = test_cases.case_id Inner Join
test_case_status On test_cases.case_status_id =
test_case_status.case_status_id
Where
test_case_runs****n_id = 1314


And these are my results. For several TC_IDs, I get multiple records back, but I only want the most recent one.

TC_ID
139 1314 1 17886 SW-5.0: Install/Upgrade/Rollback - 480i IDLE
139 1314 3 17886 SW-5.0: Install/Upgrade/Rollback - 480i FAILED
139 1314 3 17886 SW-5.0: Install/Upgrade/Rollback - 480i FAILED
139 1314 1 19088 SW-5.0: Install/Upgrade/Rollback - 480i IDLE
139 1314 2 19088 SW-5.0: Install/Upgrade/Rollback - 480i PASSED
139 1314 1 19093 SW-5.0: Install/Upgrade/Rollback - 480i IDLE
139 1314 1 19093 SW-5.0: Install/Upgrade/Rollback - 480i IDLE
139 1314 2 19093 SW-5.0: Install/Upgrade/Rollback - 480i PASSED
139 1314 1 19287 SW-5.0: Install/Upgrade/Rollback - 480i IDLE
139 1314 1 19287 SW-5.0: Install/Upgrade/Rollback - 480i IDLE
139 1314 1 19287 SW-5.0: Install/Upgrade/Rollback - 480i IDLE
139 1314 1 20469 SW-5.0: Install/Upgrade/Rollback - 480i IDLE
139 1314 3 20469 SW-5.0: Install/Upgrade/Rollback - 480i FAILED

Can I customize my query to give me only the latest record?

Thanks in advance,
Mike Thompson
Ottawa, Canada

If you have a datetime field, you may write something like:
WHERE datetime_field >= MAX(datetime_field)

of course that you need a GROUP BY on test_case_runs****n_id