Cte table with partitions

1

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!

2

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.

3

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