Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

Union with Group by is possible?

edited November 2018 in General Discussion

I have two tables like this -


i j
1 2
1 3
1 4


i j
2 1
2 5
3 1
3 6

What I want to do is I need to find (Select j From Nu UNION Select j from Nv) for every Nu.i , Nv.i. Like this -

Nu.i Nv.i v
1 2 2
1 2 3
1 2 4
1 2 1
1 2 5
1 3 2
1 3 3
1 3 4
1 3 1
1 3 6

Is there any way I can query this with Vertica SQL?

I tried -
Select Nu.i, Nv.i, (Select j from Nu UNION Select j from Nv group by Nv.j) as v from Nu, Nv;

Error: ERROR 4840: Subquery used as an expression returned more than one row
Select Nu.i, Nv.i, (Select j from Nu UNION Select j from Nv) as v from Nu, Nv group by Nu.i, Nv.i;
Error: Subqueries in the SELECT or ORDER BY are not supported if the subquery is not part of the GROUP BY

Please, let me know you suggestion


  • DaveTDaveT Employee

    I am probably not totally sure what you want to do but here is an idea.

    You have a select clause within another select clause which means only a single constant value should come out of the inner select clause. Otherwise you are trying to perform a join within the select clause.

    You could write it like the following but I don't think that is what you want want because it will cross join every combination whereas you seem to only want the rows where j is associated with either the Nu.i or the Nv.i

    select distinct Nu.i, Nv.i, v.j from Nu, Nv, (select j from Nu union select j from Nv) v;

    So, the above query gets you 2 extra rows you apparently don't want:
    1 2 6
    1 3 5

    Perhaps something like the following will work for you. I am not sure if this is the most efficient way but I think it gets you the values you want:

    select distinct Nu.i, Nv.i, Nu.j from Nu cross join Nv union select distinct Nu.i, Nv.i, Nv.j v from Nu cross join Nv;

  • You will get all combinations with this but can not guarantee ordering.

    select * from (select distinct Nu.i, Nv.i from Nu cross join Nv) N_uv cross join (select j from Nu union select j from Nv) Nw ;

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.