Clash Royale CLAN TAG#URR8PPP

Redshift this type of correlated subquery pattern is not supported due to internal error

When I run the first query all runs fine:

select uid,
(select top 1 city from UserData where UserData.uid = #uids.uid
group by city)
from #uids;


The problem is when I add order by count(city), I get an error: This type of correlated subquery pattern is not supported due to internal error

order by count(city)

select uid,
(select top 1 city from UserData where UserData.uid = #uids.uid
group by city
order by count(city))
from #uids;


2 Answers
2

The error states that your subquery is not working, which is obvious because you cannot use functions in ORDER clauses with SQL. You can only use them in SELECT and HAVING clauses.

If you want to order by count(city), you should add it to SELECT clause with an alias and order by alias name. So your subquery can be like this:

count(city)

(SELECT city, COUNT(*) AS city_count
FROM UserData
WHERE UserData.uid = #uids.uid
GROUP BY city
ORDER BY city_count
LIMIT 1)


I would do this using row_number() in RedShift:

row_number()

select u.uid, ud.city
from uids u left join
(select uid, city, count(*) as cnt,
row_number() over (partition by uid order by count(*) desc) as seqnum
from UserData ud
group by uid, city
) ud
on ud.uid = u.uid and seqnum = 1;


You might also try this in SQL Server as well.

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.