ROW_NUMBER() same values

Database
Fan

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

10 REPLIES
Enthusiast

Re: ROW_NUMBER() same values

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
Fan

Re: ROW_NUMBER() same values

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

Enthusiast

Re: ROW_NUMBER() same values

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
Fan

Re: ROW_NUMBER() same values

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

Enthusiast

Re: ROW_NUMBER() same values

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

Enthusiast

Re: ROW_NUMBER() same values

Hi Fazol,

You can use the Rank Function.

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

Thanks & Regards,

Adharssh Rao.

Enthusiast

Re: ROW_NUMBER() same values

Hi Tamil,

Can you post some sample valyes. Couldn't get the result from the sample that you have pasted.

Thanks & Regards,

Adharssh.

Teradata Employee

Re: ROW_NUMBER() same values

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

Enthusiast

Re: ROW_NUMBER() same values

thanks a lot Farhan .