HomeFlySpeed SQL QueryMySQL database, I want to display ONLY the latest records.

MySQL database, I want to display ONLY the latest records.


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.run_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.run_id = test_case_runs.run_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.run_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/720p/1080i IDLE
139 1314 3 17886 SW-5.0: Install/Upgrade/Rollback - 480i/720p/1080i FAILED
139 1314 3 17886 SW-5.0: Install/Upgrade/Rollback - 480i/720p/1080i FAILED
139 1314 1 19088 SW-5.0: Install/Upgrade/Rollback - 480i/720p/1080i IDLE
139 1314 2 19088 SW-5.0: Install/Upgrade/Rollback - 480i/720p/1080i PASSED
139 1314 1 19093 SW-5.0: Install/Upgrade/Rollback - 480i/720p/1080i IDLE
139 1314 1 19093 SW-5.0: Install/Upgrade/Rollback - 480i/720p/1080i IDLE
139 1314 2 19093 SW-5.0: Install/Upgrade/Rollback - 480i/720p/1080i PASSED
139 1314 1 19287 SW-5.0: Install/Upgrade/Rollback - 480i/720p/1080i IDLE
139 1314 1 19287 SW-5.0: Install/Upgrade/Rollback - 480i/720p/1080i IDLE
139 1314 1 19287 SW-5.0: Install/Upgrade/Rollback - 480i/720p/1080i IDLE
139 1314 1 20469 SW-5.0: Install/Upgrade/Rollback - 480i/720p/1080i IDLE
139 1314 3 20469 SW-5.0: Install/Upgrade/Rollback - 480i/720p/1080i FAILED

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

Thanks in advance,
Mike Thompson
Ottawa, Canada



Mike Thompson | email
May 3 2011, 12:48 PM
Dear Mike,

I think it's possible but I don't know what is the database server lying behind this tracking system. For example MySQL and SQLite have the LIMIT clause for that purpose. Please refer to the SQL reference documentation for particular server to learn more. Also if FlySpeed SQL Query successfully detected the database server, you may right-click at the query building area, select the Properties menu item and see what clause the tool suggests to use (otherwise this menu item will be disabled).
Sergey Smagin | email
22 hours, 21 mins since original post
This topic is closed