Results 1 to 2 of 2
  1. #1
    Join Date
    May 2011
    Posts
    1

    Red face Unanswered: NEWB: 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****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

  2. #2
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    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
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •