All Forums Database
Fazol 2 posts Joined 11/13
19 Nov 2013
ROW_NUMBER() same values

Hi All,
regarding to ROW_NUMBER(). If I have same values for part PARTITION and ORDER BY then How does "ROW_NUMBER()" decide which records will have result = 1 and result = 2?
Example of table
1. row ... col1 = abc, col2 = 1
2. row ... col1 = abc, col2 = 1
3. row ... col1 = xyz, col2 = 99
 

select
<table>.*
ROW_NUMBER() OVER(PARTITION BY col1 ORDER BY col2)
from <table>

Expected result is
abc,1,1
abc,1,2
xyz,99,1
 
many thanks
Rene

SQL Developer

M.Saeed Khurram 544 posts Joined 09/12
19 Nov 2013

Hi Fazol,
It is the order by clause that determines which row will get number one and which row number 2 within each partition. if there is any specific requirement to number a row with same values, then you will have to invove another column for setting the priority of the rows!
 

Khurram

Fazol 2 posts Joined 11/13
19 Nov 2013

Hi Khurram,
Thank you. It's true and I asked about new business rule my business side but I was just curious if it exist any internal teradata rule for these cases - e.g. hashcode of ... just idea.
 
Rene

SQL Developer

M.Saeed Khurram 544 posts Joined 09/12
19 Nov 2013

If you Col1 is an index column then Teradata will generate same rowhash for 'Abc' in both the rows and still the result will be same :)
 

Khurram

TD_WFS 2 posts Joined 02/13
19 Nov 2013

Hi
As khurram said,the same two rows will have the different row_numbers for idenity and it will diplay sorting order if you put orderby clause.
 
Thanks

TamilTiger 9 posts Joined 12/13
04 Apr 2014

hi ,
my table is like
a               b           (i need rank like)
201401    tc<tz         1
201402    tc<tz         2
201403    tc>tz         1
201405    tc>tz         2
201406     tc>tz        3
201407     tc<tz        1
please see the last one  week 201407 tc<tz   my rank should be 1 ..is it possible ?please let me know if u have ans

Adharssh 36 posts Joined 08/13
04 Apr 2014

Hi Fazol,
 
You can use the Rank Function.

select<table>.*
RANK() OVER(PARTITION BY col1 ORDER BY col2) from <table>

Thanks & Regards,
Adharssh Rao.

Share the Knowledge. Feel the Happiness, When you share/Teach it.

Adharssh 36 posts Joined 08/13
04 Apr 2014

Hi Tamil,
Can you post some sample valyes. Couldn't get the result from the sample that you have pasted.
Thanks & Regards,
Adharssh.
 

Share the Knowledge. Feel the Happiness, When you share/Teach it.

FarhanArif 7 posts Joined 05/12
04 Apr 2014

Hi Tamil,
From what I've understood from your data, whenever 'b' changes from 'tc<tz' to 'tc>tz' or viceversa you want a new sequence to begin.
For that you can use a RESET WHEN clause that keeps comparing with the last value of 'b'.

SELECT 
	COLUMN1, COLUMN2
	,ROW_NUMBER() OVER (
				ORDER BY COLUMN1 
				RESET WHEN COLUMN2 <> --The Previous Value of Column2 --
				MIN(COLUMN2) OVER (ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING ))
FROM TABLE1

The output will be:
COLUMN1    COLUMN2    Row_Number()
201401    tc<tz    1
201402    tc<tz    2
201403    tc>tz    1
201405    tc>tz    2
201406    tc>tz    3
201407    tc<tz    1
Hope it helps.
Farhan

 

TamilTiger 9 posts Joined 12/13
08 Apr 2014

thanks a lot Farhan .

TamilTiger 9 posts Joined 12/13
26 May 2015

Hi need help
Table

col1          col2 col3   col 4   calculatedcolumn
201301      1      ?      ?           ?
201302       1    ?       ?           ?
201303       1   4.5      3        4.5*12+3(col4) so my value is 57
201304       1    ?        2            57*12+2           686  
201305      1      ?       5          686*12+5        8237
201301    2       ?         1              ?   
201302    2       3.1      2          3.1*12+1
 
if value present in col3 then use that value for calculated field and use perivious calculated value for next week(next row till combination of col1 and col2 ) please help me ,it may  procedure or query anything

You must sign in to leave a comment.