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,

Juan Carlos Oropeza · Accepted Answer · 2015-10-16 14:58:25Z

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

answered Oct 16, 2015 at 14:58

Juan Carlos Oropeza

48.4k14 gold badges87 silver badges128 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Siyual · Accepted Answer · 2017-06-26 12:46:44Z

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

edited Jun 26, 2017 at 12:46

answered Oct 16, 2015 at 14:37

Siyual

17k8 gold badges47 silver badges60 bronze badges

2 Comments

Siyual Over a year ago

It aliases the sub-query so that it can be referenced.

Jad Over a year ago

I was trying to do the same with INTO #temp,

Collectives™ on Stack Overflow

SQL Server 2005 sub query

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related