SQL Query to find Rank

Question

I have a table with Child_ID, Test_ID and Test_Date

 Child_ID Test_ID     Test_Date
   1        1        2018-05-21
   1        2        2018-05-22  

I want to find TestNumber so I am using Rank on fly

Select Child_ID, Test_ID ,Test_Date,RANK() OVER (PARTITION BY t.Child_id order by Test_Date asc,boe.Test_ID asc) AS [TEST_NUMB]
from [Test] T
where t.Child_ID in (1)

which give me result

Child_ID Test_ID     Test_Date    TEST_NUMB
1           1        2018-05-21       1
1           2        2018-05-22       2

But the issue is when I select only one test ID then the Test_number gives '1' everytime

select * from Test where test_ID =1 

    Child_ID Test_ID     Test_Date    TEST_NUMB
    2560249     1        2018-05-21       1

select * from Test where test_ID =2 

    Child_ID Test_ID     Test_Date    TEST_NUMB
    2560249     1        2018-05-21       1

Can I get a query to get the exact test_number according to Test_Date with out inserting to a temp table (I have millions of records)

Answer 1

Same are CTE from Tim

select * 
from ( Select Child_ID, Test_ID , Test_Date
            , RANK() OVER (PARTITION BY t.Child_id 
                           order by Test_Date asc, Test_ID asc) AS [TEST_NUMB]
        from [Test] T
       where Child_ID = 1 
     ) tt 
where Test_ID = 1

Answer 2

Place your current query into a CTE, and then query that:

WITH cte AS (
    SELECT Child_ID, Test_ID ,Test_Date,
        RANK() OVER (PARTITION BY Child_id ORDER BY Test_Date, Test_ID) AS [TEST_NUMB]
    FROM [Test]
    WHERE Child_ID IN (1)
)

SELECT * FROM cte WHERE test_ID = 1;

This should work because at the time you query by an exact test_ID, the rank values will already have been materialized (sorry, I just wanted to use that word).