ROW_NUMBER() with Qualify clause

Moderator: NorbertKrupa

Post Reply
User avatar
nnani
Master
Master
Posts: 302
Joined: Fri Apr 13, 2012 6:28 am
Contact:

ROW_NUMBER() with Qualify clause

Post by nnani » Thu May 30, 2013 3:30 pm

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.
nnani........
Long way to go

You can check out my blogs at vertica-howto

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: ROW_NUMBER() with Qualify clause

Post by JimKnicely » Thu May 30, 2013 4:33 pm

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)
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

User avatar
nnani
Master
Master
Posts: 302
Joined: Fri Apr 13, 2012 6:28 am
Contact:

Re: ROW_NUMBER() with Qualify clause

Post by nnani » Fri May 31, 2013 6:21 am

Just, what I was looking for ......
Thanks knicely. :)
nnani........
Long way to go

You can check out my blogs at vertica-howto

User avatar
usli06
Intermediate
Intermediate
Posts: 93
Joined: Wed Jan 25, 2012 4:53 am

Re: ROW_NUMBER() with Qualify clause

Post by usli06 » Fri May 31, 2013 5:58 pm

What is TD? Touchdown?

User avatar
nnani
Master
Master
Posts: 302
Joined: Fri Apr 13, 2012 6:28 am
Contact:

Re: ROW_NUMBER() with Qualify clause

Post by nnani » Mon Jun 03, 2013 12:32 pm

Hello,

TD stands for Teradata.....
nnani........
Long way to go

You can check out my blogs at vertica-howto

Post Reply

Return to “Vertica Analytics”