CODEWITHSUNDEEP
sql-server
charles freeman
charles freeman

Reputation: 9

Can data in a cte become lower than their actual value

This is my problem I am trying to understand how a column which has values higher than 1000 is reduced to values below 1000 in my CTE that is the table transaction-id

This is my code:

with productionCTE(quantity, transactionid, productid) as
(
     select 
         quantity, ProductID, max(transactionid)
     from 
         Production.TransactionHistory
     group by
         Quantity, ProductID
) 
select 
    quantity, transactionid,
    productid = (select max(transactionid) as MAX_UNIT from productionCTE)
from
    productionCTE

Upvotes: 0

Views: 38

Answers (1)

forpas
forpas

Reputation: 164174

The order of the columns you declare for the cte is this:

with productionCTE(quantity, transactionid, productid)

and then the main body of the cte:

select quantity,ProductID,max(transactionid) 
from Production.TransactionHistory 
GROUP BY Quantity,ProductID

So the quantity is ok, but ProductID which is selected 2nd becomes transactionid in the results because this is what you declared as 2nd column in the cte header and vice versa for the 3d column.
So what you see as ProductID is actually transactionid.
Change the declaration to:

with productionCTE(quantity, productid, transactionid)

Then here:

select QUANTITY,TRANSACTIONID ,
PRODUCTID  = (SELECT MAX(TRANSACTIONID) AS MAX_UNIT FROM productionCTE)
FROM productionCTE

why are you assigning the max transactionid to productid?
Is it because you get wrong results with the cte?
The only thing that would make sense is this:

select QUANTITY,PRODUCTID ,
  TRANSACTIONID = (SELECT MAX(TRANSACTIONID) AS MAX_UNIT FROM productionCTE)
FROM productionCTE

although I don't know what you are trying to do.

Upvotes: 2

Related Questions