Qualify Row_Number

Database

Qualify Row_Number

Hello,

 

Can you help me, please? I have a problem with this Query, it doesn't work.:

 

I would like to join two tables by "email" variable, but in second table TabB I need choose only one row - probably using the function Qualify Row_Number. In first Table TabA can be the unique "email" for more ID. The result should be all of ID with this email from TabB.

SELECT TabA.id
    from TabA
    where email in (select email
    from TabB
    where  response in ('RESP3', 'RESP4')
    QUALIFY ROW_NUMBER() OVER (Partition BY email ORDER BY response_dttm DESC) = 1  and response = 'RESP3' and email = 'abc@yyyy.com')

Thank you,

 

Martin

5 REPLIES 5
Teradata Employee

Re: Qualify Row_Number

Hi Martin,

 

If you could provide some rows (like ten per table) with data it should be easier to help you.

Re: Qualify Row_Number

Hi,

thank you much, here is test data...

 

TabA     TabB   
IDemail    IDemailresponseresponse_dttm
1abc@yyy.com    1abc@yyy.comRESP101.06.2019
2abc@yyy.com    1abc@yyy.comRESP202.06.2019
3abc@yyy.com    1abc@yyy.comRESP310.06.2019
4zzz@y.com    1abc@yyy.comRESP409.06.2019
5aaa@x.com    1abc@yyy.comRESP501.06.2019
6aaa@x.com    5aaa@x.comRESP103.06.2019
7aaa.bbb@y.com    5aaa@x.comRESP204.06.2019
8aaa.bbb@y.com    5aaa@x.comRESP305.06.2019
9email@email.com    7aaa.bbb@y.comRESP101.06.2019
10email@email.com    10email@email.comRESP101.06.2019

 

M.

Teradata Employee

Re: Qualify Row_Number

Oh ok, it's the qualify that is not allowed in subqueries.

 

You can use a join :

with cte_tabB (email) as
(
 select email
   from TabB
  where response in ('RESP3', 'RESP4')
qualify row_number() over (partition by email order by response_dttm desc) = 1 
    and response = 'RESP3'
)
select ta.id, ta.email
  from TabA as ta
  join cte_tabB as tb on tb.email = ta.email;

 

Re: Qualify Row_Number

Great, it works.

 

Thank you so much and I'm sorry, but I'm a beginner :)

 

M.

Highlighted
Teradata Employee

Re: Qualify Row_Number

We've all been beginners, no worries :)