ROW_NUMBER( ) OVER in impala

sql window-functions impala
user1189851 picture user1189851 · Oct 6, 2014 · Viewed 29k times · Source

I have a use case where I need to use ROW_NUMBER() over PARTITION: Something like:

SELECT
  Column1 , Column 2
  ROW_NUMBER() OVER (
    PARTITION BY ACCOUNT_NUM
    ORDER BY FREQ, MAN, MODEL) as LEVEL
FROM
  TEST_TABLE

I need a workaround for this in Impala. Unfortunately Impala does not support sub queries and also does not support ROW_NUMBER() OVER functionality. Thank you for your help.

Answer

Tagar picture Tagar · Nov 8, 2016

ROW_NUMBER() OVER PARTITION was added in CDH 5.2:

https://www.cloudera.com/documentation/enterprise/latest/topics/impala_analytic_functions.html#row_number

ROW_NUMBER() OVER([partition_by_clause] order_by_clause)

Related questions

What's the difference between RANK() and DENSE_RANK() functions in oracle?

What's the difference between RANK() and DENSE_RANK() functions? How to find out nth salary in the following emptbl table? DEPTNO EMPNAME SAL ------------------------------ 10 rrr 10000.00 11 nnn 20000.00 11 mmm 5000.00 12 kkk 30000.00 10 fff 40000.00 10 ddd 40000.00 10 bbb 50000.00 10 ccc 50000.00 If in the table data having nulls, what …

Read More
Oracle "Partition By" Keyword

Can someone please explain what the partition by keyword does and give a simple example of it in action, as well as why one would want to use it? I have a SQL query written by someone else and I'm …

Read More
Select Row number in postgres

How to select row number in postgres. I tried this: select row_number() over (ORDER BY cgcode_odc_mapping_id)as rownum, cgcode_odc_mapping_id from access_odc.access_odc_mapping_tb order by cgcode_odc_mapping_id and …

Read More