Query optimisation: Select min of a result set where a column is max of another set

Question

I am trying to optimise my query and have got a little stuck.

Please consider the following table structure. I have also included some SQL in case you wish to build the table yourself:

| id | test_run_id | test_case_id |  test_step_id | test_result |
|----|-------------|--------------|---------------|-------------|
| 1  |      1      |       3      |      1        |      1      |
| 2  |      1      |       3      |      2        |      1      |
| 3  |      1      |       3      |      3        |      0      |
| 4  |      2      |       3      |      1        |      1      |
| 5  |      2      |       3      |      2        |      1      |
| 6  |      2      |       3      |      3        |      1      |

CREATE TABLE test_results(
    id INT(10) AUTO_INCREMENT,
    test_run_id INT(10) DEFAULT 0,
    test_case_id INT(10) DEFAULT 0,
    test_step_id INT(10) DEFAULT 0,
    test_result  CHAR(1) DEFAULT 0,
    PRIMARY KEY(id)
);

INSERT INTO test_results(
    `test_run_id`,
    `test_case_id`,
    `test_step_id`,
    `test_result`
) VALUES (
    1,
    3,
    1,
    1
), (
    1,
    3,
    2,
    1
), (
    1,
    3,
    3,
    0
), (
    2,
    3,
    1,
    1
), (
    2,
    3,
    2,
    1
), (
    2,
    3,
    3,
    1
);

Now, I want to get the most recent set of results as part of my query. And I know I could do that like this:

SELECT
    MIN( test_result ) as 'last_run_lowest'
FROM test_run_results
WHERE test_case_id=tc.id
AND test_run_id=(
    SELECT
        MAX( test_run_id )
    FROM test_run_results
    WHERE test_case_id=tc.id
)

And that would return me a single row:

| last_run_lowest |
|-----------------|
|       1         |

Which is what I want to do, as the value 1 means the last test run passed. However, doing things this way is taking up quite some time because of the amount of results in my test_run_results table.

What I would like to find out, is if there is a more efficient way to return the same data, so that the query does not take such a long time to run.

Thanks in advance

Answer 1

Why not just do it this way, if you only need the single value?

SQL Server version:

Select Top 1 test_result
from test_results
order by Test_run_ID desc, test_result asc

Per comments, MySQL version:

Select test_result
from test_results
order by Test_run_ID desc, test_result asc
limit 1;