CODEWITHSUNDEEP
sqlvertica
T Amin
T Amin

Reputation: 55

Vertica - Union with Group by is possible?

I have two tables like this :

Nu

i j
1 2
1 3
1 4

Nv

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 Distinct (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

And : 

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.

Upvotes: 1

Views: 256

Answers (2)

Basil Peace
Basil Peace

Reputation: 411

Your general idea to use UNION and CROSS JOIN is correct, you just need to do it in reverse order:

WITH                                                                                                                                           
Nu (i,j) AS (
          SELECT 1,2
UNION ALL SELECT 1,3
UNION ALL SELECT 1,4
)
,
Nv(i,j) AS (
          SELECT 2,1
UNION ALL SELECT 2,5
UNION ALL SELECT 3,1
UNION ALL SELECT 3,6
)
SELECT
  Nu.i AS "Nu.i",
  Nv.i AS "Nv.i",
  Nu.j
FROM
  Nu, Nv
UNION
SELECT
  Nu.i,
  Nv.i,
  Nv.j
FROM
  Nv, Nu;

Upvotes: 0

marcothesane
marcothesane

Reputation: 6749

Your result table is somehow unexpected to me - I can't figure out the rules on how you would want it to be generated.

Nu has 3 rows. Nv has 4 rows. I was thinking of a CROSS JOIN between the two tables, but that would lead to 3 x 4, that's 12 rows. Like so:

WITH                                                                                                                                           
Nu (i,j) AS (
          SELECT 1,2
UNION ALL SELECT 1,3
UNION ALL SELECT 1,4
)
,
Nv(i,j) AS (
          SELECT 2,1
UNION ALL SELECT 2,5
UNION ALL SELECT 3,1
UNION ALL SELECT 3,6
)
SELECT
  Nu.i AS "Nu.i"
, Nv.i AS "Nv.i"
, Nu.j AS "Nu.j"
, Nv.j AS "Nv.j"
FROM Nu CROSS JOIN Nv;
-- out  Nu.i | Nv.i | Nu.j | Nv.j 
-- out ------+------+------+------
-- out     1 |    2 |    2 |    1
-- out     1 |    2 |    3 |    1
-- out     1 |    2 |    4 |    1
-- out     1 |    2 |    2 |    5
-- out     1 |    2 |    3 |    5
-- out     1 |    2 |    4 |    5
-- out     1 |    3 |    2 |    1
-- out     1 |    3 |    3 |    1
-- out     1 |    3 |    4 |    1
-- out     1 |    3 |    2 |    6
-- out     1 |    3 |    3 |    6
-- out     1 |    3 |    4 |    6
-- out (12 rows)
-- out 
-- out Time: First fetch (12 rows): 14.037 ms. All rows formatted: 14.086 ms

But could it be that, seeing this result table, you can figure out the rest by yourself?

Upvotes: 0

Related Questions