ROW_NUMBER() OVER (PARTITION BY col1,col2.... order by col1)

General

ROW_NUMBER() OVER (PARTITION BY col1,col2.... order by col1)

Hi,

I want to know use of ROW_NUMBER() OVER (PARTITION BY col1,col2.... order by col1), please explain in detail with example.

8 REPLIES

Re: ROW_NUMBER() OVER (PARTITION BY col1,col2.... order by col1)

The use of the function is limited by the imagination of the developer.  The row_number just does that numbers the row partitioned by col1, col2 order by col1.  Did you try out this function on a table with smaller number of rows? Your best teacher is your own sql that you type and run and it's even better when you encounter errors doing so. Hope this helps! -Sankar

Re: ROW_NUMBER() OVER (PARTITION BY col1,col2.... order by col1)

ROW_NUMBER is very similar to RANK function but for the following difference.

When the column on which RANK is performed contains identical values, the same RANK is provided to records with identical values. The next value (record) will be assigned a RANK reflecting the number number identical values before it.

Example:

RANK

Prod_id   Units sold   RANK

01            100           1

05            95             2

02            95             2

04            90             4    -> not 3

03            88             5

ROW_NUMBER does not bother about the identical values but numbers the rows with identical values as per the order mentioned by the ORDER BY.

Example:

ROW_NUMBER 

Prod_id   Units sold   RANK

01            100           1

02            95             2

05            95             3

04            90             4 

03            88             5

Re: ROW_NUMBER() OVER (PARTITION BY col1,col2.... order by col1)

How do we use Row_Number if our rows returned ends up larger than the 'Integer' limit on it?

Re: ROW_NUMBER() OVER (PARTITION BY col1,col2.... order by col1)

why dont you cast it to decimal if the column exceeds the integer limit.

Re: ROW_NUMBER() OVER (PARTITION BY col1,col2.... order by col1)

Hi,

You can cast it to BIGINT, and if still the range is outside the limit, then cast it to Decimal(18,0).

Khurram

Re: ROW_NUMBER() OVER (PARTITION BY col1,col2.... order by col1)

Hi ,

If row_number is use for just assigning the sequence , than what is the use of it ?

I mean where we actually have to use it .

Please give some real time example .

Thanks

SJ

Re: ROW_NUMBER() OVER (PARTITION BY col1,col2.... order by col1)

SELECT a.col1
,CASE
WHEN b.cust_id IS NULL
THEN 0
ELSE 1
END AS is_true
FROM DB1.table1 AS a
LEFT JOIN DB2.table2 AS b
ON (
a.col3 = b.cust_id
AND a.start_date BETWEEN '2015-01-01' AND '2015-01-02'
) QUALIFY 1 = ROW_NUMBER() OVER (
PARTITION BY a.col1 ORDER BY b.col4
,b.col5
)
WHERE a.start_date BETWEEN '2015-01-01' AND '2015-01-02'

Is there any way to optimize the above query. Its taking so much CPU time 

Re: ROW_NUMBER() OVER (PARTITION BY col1,col2.... order by col1)

You can use a secondary index.

Plus you can try collecting stats;check the explain plan if there are other methods to optimise like decreasing number of joins etc.