TSQL Subquery in from

Question

I am attempting to do a subquery which should work but I am missing something in the syntex.

I am trying:

select * 
from (select * from (select *, row_number() over (partition by number,system order by number,system) as rc from [dbo].[info]) tk0 where tk0.rc =1)  tkt
 inner join [dbo].[QUEUES] pq
 on pq.[QUEUE_NAME] = tkt.[QueueName] inner join [dbo].PLATFORMS] pl
 on pl.id = pq.platform_id

and I get incorrect syntax near inner.

This works:

select *, row_number() over (partition by number,system order by number,system) as rc from [dbo].[info]) tk0 where tk0.rc =1

Answer 1

just missing a [ in platforms line .The below should work:

select * 
from 
    (
        select * 
        from 
            (
                select 
                    *, row_number() over (partition by number,system order by number,system) as rc 
                from [dbo].[info]
            ) tk0 
        where tk0.rc =1
    )  tkt
 inner join [dbo].[QUEUES] pq
 on pq.[QUEUE_NAME] = tkt.[QueueName] 
 inner join [dbo].[PLATFORMS] pl --correction done here
 on pl.id = pq.platform_id

also you can write your where clause in join too:

select * 
from 
    (
    select 
        *, row_number() over (partition by number,system order by number,system) as rc 
    from [dbo].[info]

    )  tkt
 inner join [dbo].[QUEUES] pq
 on pq.[QUEUE_NAME] = tkt.[QueueName]  and tkt.rc =1
 inner join [dbo].[PLATFORMS] pl
 on pl.id = pq.platform_id