You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@phoenix.apache.org by Cheyenne Forbes <ch...@gmail.com> on 2017/05/03 23:57:22 UTC
ORDER BY not working with UNION ALL
I get "Undefined column family. familyName=f" whenever I run the following
query,it works without the ORDER BY and works with the ORDER BY if its not
a union and just one select statement
SELECT
p.name
FROM
person p
JOIN
friends f
ON
f.person = p.id
WHERE
567 != ANY(f.persons)
UNION ALL
SELECT
p.name
FROM
person p
JOIN
friends f
ON
f.person = p.id
WHERE
123 != ANY(f.persons)
ORDER BY f.date_time LIMIT 20
Regards,
Cheyenne O. Forbes
Re: ORDER BY not working with UNION ALL
Posted by Sergey Soldatov <se...@gmail.com>.
Well, even if you don't use family, you will get an error that column
date_time is undefined. Consider the result of UNION ALL as a separate
table and ORDER BY is applied to this table. You don't have column
date_time there. Don't forget that UNION ALL may work with different
tables, so there may be no date_time column in the second table.
So you need a construction something like :
select p.name from (select p.name, f.date_time .... UNION ALL select
p.name.f.date_time .... ORDER BY f.date_time LIMIT 20)
Thanks,
Sergey
On Wed, May 3, 2017 at 4:57 PM, Cheyenne Forbes <
cheyenne.osanu.forbes@gmail.com> wrote:
> I get "Undefined column family. familyName=f" whenever I run the
> following query,it works without the ORDER BY and works with the ORDER BY
> if its not a union and just one select statement
>
> SELECT
> p.name
> FROM
> person p
> JOIN
> friends f
> ON
> f.person = p.id
> WHERE
> 567 != ANY(f.persons)
> UNION ALL
> SELECT
> p.name
> FROM
> person p
> JOIN
> friends f
> ON
> f.person = p.id
> WHERE
> 123 != ANY(f.persons)
> ORDER BY f.date_time LIMIT 20
>
> Regards,
>
> Cheyenne O. Forbes
>