All Forums General
Amit Kumar 14 posts Joined 08/11
18 Jun 2012
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.

 

TDDeveloper 23 posts Joined 09/09
18 Jun 2012

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

sidswami 6 posts Joined 05/12
24 Jun 2012

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

Thanks,
Siddharth.

MarkVYoung 12 posts Joined 03/12
30 Mar 2013

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

gkatiyar 19 posts Joined 09/13
21 Sep 2013

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

Regards,
Gaurav Katiyar

M.Saeed Khurram 544 posts Joined 09/12
22 Sep 2013

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

Khurram

Sagar_J 1 post Joined 11/14
02 Jan 2015

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

28 Jan 2015

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 

Saranya

You must sign in to leave a comment.