Oracle SQL, how to constrain on join?

Question

This is HP/IBM Quality Centre;

There are three tables.

A testcycl has test and a test has steps. Steps have many duplicates by nature of having many runs.

Therefore, for a given TestCycle, I would like to get all of the tests including their steps, for the most recent st_run_id.

Have been playing around with ways to constrain it, but nothing is working out for me. 1 Cycle could have 500 tests which yield 5000 steps due to duplicate runs, it's really easy to get stuck.

How to constrain the steps query based most recent st_run_id, keeping in mind that it has to be constrained by st_test_id?

select *
from
(
    -- Get details for that test
    select
        ts_test_id,
        ts_name,
        ts_description
    from test
    where 1=1
    and ts_test_id in
    (
            -- Get all test ID's belonging to a specific cycle
            select distinct tc_test_id
            from testcycl
            where 1=1
            and testcycl.tc_assign_rcyc = 1021
    )
) tests
-- Get the steps
left join
(
    select
        step.st_test_id,
        step.st_run_id,
        step.st_execution_date,
        step.st_id,
        step.st_step_name,
        step.st_description,
        step.st_expected
    from step
    where 1=1
) steps
on tests.ts_test_id = steps.st_test_id

Answer 1

This should do what you want:

left join
(select s.*,
        row_number() over (partition by s.st_test_id order by st.st_run_id desc) as seqnum
) steps
on tests.ts_test_id = steps.st_test_id and steps.seqnum = 1

Answer 2

You should use direct tables instead of subquery as follows:

Select * from
(select t.*, s.*,
        row_number() over (partition by s.st_test_id order by s.st_run_id desc nulls last) as rn
    from test t 
    Left join steps s on t.ts_test_id = s.st_test_id 
    where 1=1
    and t.ts_test_id in
    (
            -- Get all test ID's belonging to a specific cycle
            select distinct tc_test_id
            from testcycl
            where 1=1
            and testcycl.tc_assign_rcyc = 1021
    )
) Where rn = 1;

You can take only that columns in select clause which you require.