Skip to main content

Analytic function for conditional reset ROW_NUMBER when a second column has a NULL value

  • February 28, 2015
  • 1 reply
  • 12 views

Heikik

Hello All,
I have in TeraData :

 

Select PER_ID, CONTACT_ID, START_DT, PREV_CONTACT_ID,
ROW_NUMBER() Over (Partition By PER_ID Order By START_DT RESET WHEN PREV_CONTACT_ID Is Null) As CONTACT_SERIES
 
Explanation: if in partition a column PREV_CONTACT_ID has a value NULL, in this case the ROW_NUMBER must restarts from 1.


But in Vertica does not exists: 'OVER (.... RESET WHEN condition)'. How can I write similar condition in Vertica?

Thanks,
Heiki

1 reply

ar_
  • New Participant
  • March 1, 2015

Hi Heikik,

 

This part could be helpful: https://my.vertica.com/docs/6.0.x/HTML/index.htm#13612.htm