CODEWITHSUNDEEP
sql-server
dutchlab
dutchlab

Reputation: 590

Join a MS SQL Server WITH statement into a Select statement

I received this answer to this question MS SQL Server Last User Logged in Multiple Clients with Multiple Users and it works great. 

;with cte as
(
    select 
        client, myuser, lastlogin, 
        row_number() over (partition by client order by lastlogin desc) r# 
    from 
        @mytable 
)
select * 
from cte 
where r# = 1

How do I get this joined to a regular Select statement that selects data from other tables also?

For example:

 SELECT t1.id, t2.name
 FROM table1 t1
 JOIN table2 t2 ON (t2.id = t1.id)
 WHERE t1.id = 1

Upvotes: 0

Views: 47

Answers (1)

M. Kanarkowski
M. Kanarkowski

Reputation: 2195

There is no restriction, you can just join a result of a cte with other tables. Cte is a subquery, but it makes your code more readable.

 ;with cte as(
 select client,myuser,lastlogin,row_number() over(partition by client order by lastlogin desc) r# 
 from @mytable 
)
 SELECT t1.id, t2.name
 FROM table1 t1
 JOIN table2 t2 ON (t2.id = t1.id)
 JOIN cte t3 ON (...)
 WHERE t1.id = 1

This is the same as a query with cte.

 SELECT t1.id, t2.name
 FROM table1 t1
 JOIN table2 t2 ON (t2.id = t1.id)
 JOIN ( select client,myuser,lastlogin,row_number() over(partition by client order by lastlogin desc) r# 
 from @mytable) t3 ON (...)
 WHERE t1.id = 1

Upvotes: 1

Related Questions