CODEWITHSUNDEEP
sql-serversql-server-2005
Saleh
Saleh

Reputation: 3032

write a query without CTE

How can write this query widout CTE?

with cte as(
select FK#GharardadeAsli,MAX(PK#Shenase) as PK#Shenase,Max(TarikheSabt) as TarikheSabt from TBL#Gharardad where FK#GharardadeAsli is not null group by FK#GharardadeAsli
)
select * from v#gharardad inner join cte on cte.PK#Shenase = v#gharardad.[Shenase]

Upvotes: 1

Views: 1461

Answers (3)

Leons
Leons

Reputation: 2674

You can write it as a sub-query.

select * 
  from v#gharardad 
  inner join (select FK#GharardadeAsli,
                     MAX(PK#Shenase) as PK#Shenase,
                     Max(TarikheSabt) as TarikheSabt 
                from TBL#Gharardad 
               where FK#GharardadeAsli is not null 
               group by FK#GharardadeAsli
             ) sub on sub.PK#Shenase = v#gharardad.[Shenase]

Upvotes: 1

HMD
HMD

Reputation: 57

Declare Table Variable And Do The Same Thing

Upvotes: -1

Lieven Keersmaekers
Lieven Keersmaekers

Reputation: 58491

What about

SELECT  *
FROM    (
          select  FK#GharardadeAsli
                  , MAX(PK#Shenase) as PK#Shenase
                  , Max(TarikheSabt) as TarikheSabt 
          from    TBL#Gharardad 
          where   FK#GharardadeAsli is not null 
          group by FK#GharardadeAsli
        ) v
        INNER JOIN (
          select  FK#GharardadeAsli
                  , MAX(PK#Shenase) as PK#Shenase
                  , Max(TarikheSabt) as TarikheSabt 
          from    TBL#Gharardad 
          where   FK#GharardadeAsli is not null 
          group by FK#GharardadeAsli
        ) cte ON cte.Shenase = v.Shenase

Upvotes: 2

Related Questions