SQL Server 2005 sub query

Question

I have a wo_records table, want to be able to extract the current work order for each machine with the start time.

I wrote this query to rank each records and gives the latest entry a rank of 1, but I'm unable to nest it where I can use a where clause (where rank =1)

SELECT  
    *,
    RANK() OVER (PARTITION BY get_address ORDER BY t_start desc) AS    Last_value
FROM  
    wo_records 

Output:

ndx|Wo        | t_start                 |t_end               | get_address| Rank
--------------------------------------------------------------------------------
45  12521231    2019-01-07 15:41:24.000 NULL                    44           1
46  12521231    2018-01-08 15:42:24.000 2018-01-08 15:47:24.000 44           2
39  12521231    2016-01-21 15:43:24.000 2016-01-21 15:49:24.000 44           3

What is the correct way to nest this statement to retrieve only the rows with rank= 1?

Thanks,

Answer 1

Unless I'm missing something, all you're looking for is this?

Select  *
From    
(
    Select  *,
            RANK() over ( PARTITION BY get_address order by t_start desc)  AS    Last_value
    From    wo_records 
) As A
Where A.Last_value = 1

Answer 2

Siyual answer looks great.

I just want show you about CTE. This way you can have several derivated tables also with alias and easy to read.

WITH alias1 AS (
   Select  *,
            RANK() over ( PARTITION BY get_address order by t_start desc) AS Last_value
   From    wo_records 
), 
anotherAlias AS (
    SELECT * ....
)
Select  *
From  alias1 A   -- optional can also include [anotherAlias]
Where A.Last_value = 1