Page 1 of 1

ROW_NUMBER() with Qualify clause

Posted: Thu May 30, 2013 3:30 pm
by nnani
Hello All,

In TD the the select clause uses a ROW_NUMBER function differently from Vertica

The ROW_NUMBER function assigns a unique number to the rows starting from 1 with thewindows partition clause

The TD way to write this function

Code: Select all

  sel a.pm_id, a.pm_name
from loc_table a qualify row_number() over(partition by pm_id
order by  pm_name asc) =1
Can we write it this way in Vertica, I tried it but the qualify keyword is not taken by Vertica and the from Clause has to be at the end.
Can anybody explain what the above query does and how can we achieve the same in Vertica.

Re: ROW_NUMBER() with Qualify clause

Posted: Thu May 30, 2013 4:33 pm
by JimKnicely
Hi,

I believe that your query using the QUALIFY keyword is selecting the first row of the partitioned data.

If so, the same can be done in Vertica...

Using this data:

Code: Select all

dbadmin=> select * from loc_table order by pm_id, pm_name;
 pm_id | pm_name  
-------+----------
     1 | Jane
     1 | Jim
     1 | Josh
     2 | Bill
     2 | Helen
     3 | Beth
     3 | ChumChum
     3 | Gary
     3 | Jim
(9 rows)
Here's one way:

Code: Select all

dbadmin=> select pm_id, pm_name
dbadmin->   from (select pm_id, pm_name,
dbadmin(>                row_number() over (partition by pm_id order by pm_id, pm_name) rn
dbadmin(>           from loc_table) foo
dbadmin->  where rn = 1
dbadmin->  order 
dbadmin->     by pm_id, pm_name;
 pm_id | pm_name 
-------+---------
     1 | Jane
     2 | Bill
     3 | Beth
(3 rows)
And here's another way:

Code: Select all

dbadmin=> select distinct pm_id, first_value(pm_name) over (partition by pm_id order by pm_id, pm_name) from loc_table;
 pm_id | ?column? 
-------+----------
     1 | Jane
     2 | Bill
     3 | Beth
(3 rows)

Re: ROW_NUMBER() with Qualify clause

Posted: Fri May 31, 2013 6:21 am
by nnani
Just, what I was looking for ......
Thanks knicely. :)

Re: ROW_NUMBER() with Qualify clause

Posted: Fri May 31, 2013 5:58 pm
by usli06
What is TD? Touchdown?

Re: ROW_NUMBER() with Qualify clause

Posted: Mon Jun 03, 2013 12:32 pm
by nnani
Hello,

TD stands for Teradata.....