Teradata SQL Test – 5

Next Question is easy and the answer is easier however some of you may find it tricky. We have two tables both have just one column in it.

INPUT:

Tablename: TABLE1 & TABLE2 | Columnname: RDBMS1 & RDBMS2| Value: Teradata

Teradata SQL Test-5-Input
Teradata SQL Test-5-Input

OUTPUT:

Teradata SQL Test-5-Output
Teradata SQL Test-5-Output

Write a generic SQL code which should give required Result. Leave your answer as comment below.

11 thoughts on “Teradata SQL Test – 5

  1. sel tab1.rdbms1 ,tab2.rdbms2 from

    (sel t.*, row_number() over (partition by rdbms1 order by rdbms1) as n1 from table1 t ) tab1
    left outer join
    (sel tt.*, row_number() over (partition by rdbms2 order by rdbms2) as n2 from table2 tt ) tab2

    on tab1.n1 =tab2.n2

  2. sel distinct a.*,case when b.rdbms2 =” then null else b.rdbms2 end as rdbms2 from
    table1 a
    join table2 b
    on b.rdbms2 is not null
    order by rdbms2 desc

  3. select distinct a.rdbms,b.rdbms from table1 a, (select rdbms from table2 union select null from dual) b

  4. Solution:

    Generic query

    select A.a,B.b from
    (select RDBMS1 as a, row_number() over (order by RDBMS1) rnk from Table1) A
    left join
    (select RDBMS2 as b, row_number() over (order by RDBMS2) rnk from Table2) B
    on A.rnk=B.rnk

  5. Hi,
    Suppose i have table1 is having 100 rows and table2 is having 50 rows.then with the case statement you compare 100 values is it possible if tables are having more number of rows?

    1. Hi Poorna
      What kind of comparison you intent to do ?
      With the help of joins you can compare billions n billions of rows.

      Cheers
      Raj

  6. SELECT RDBMS1,RDBMS2 FROM (
    SELECT RDBMS1,ROW_NUMBER() OVER (PARTITION BY RDBMS1 ORDER BY RDBMS1) RNK,
    CASE WHEN RNK=1 THEN RDBMS2 ELSE ‘NULL’ END AS RDBMS2
    FROM TABLE1 T1
    LEFT OUTER JOIN TABLE2 T2
    ON
    RDBMS1=RDBMS2

    )X

    1. Hi Pravin

      Kudos. That’s right. However can you think of another way to get the same result without using any OLAP function ?

      Cheers
      Raj

      1. select a.val,case cnt when 1 then b.rdbms2 else NULL end as rdbms2
        from
        (select rdbms1 as val,count(*) cnt from table1 group by 1
        union
        select rdbms2 as val,count(*) cnt from table2 group by 1)a
        left outer join table2 b on a.val = b.rdbms2;

Leave a Reply

Your email address will not be published. Required fields are marked *