SQLTeam.com | Weblogs | Forums

Cte table with partitions

Hello!

I have this query:

with cte as

(select t.ktokk, t.txt30,
case
when t.spras='P'then '1'
when t.spras='E' then '2'
else '3'
end as ord_ktokk
from t077y t
order by ord_ktokk )

select *, rank() over(partition by t.ktokk order by ord_ktokk ASC) as osis
FROM cte ---- this part is giving an error.

I wanted to make a table that give me the distinct values of 't.ktokk' order by 'ord_ktokk'

Thanks for the help!

What's the actual error message you're getting?

The only thing that's questionable is the ORDER BY ord_ktokk in the CTE definition. I can't remember if that's syntactically invalid, but in any case it's unnecessary. It will either be ignored, or have unwanted performance overhead.

For one thing, "t." is not defined as an alias in the query. You need to add that alias in the final query, or remove the "t.":
...
select *, rank() over(partition by t. ktokk order by ord_ktokk ASC) as osis
FROM cte t ---- this part is giving an error.

1 Like