All Forums Database
aruku 1 post Joined 03/11
06 Feb 2014
Ranking the values ignoring duplicates

Hello all,
I have to select all the records of top 20 values in a field. Each value may have thousands of records. So, these values have many duplicates.
 
Below is a sample dataset and I would need the values as highlighted with expected value.
 
Order diff   lift            rank              row_number           expected_value
 -1              1.2             1                        1                             1
 -1              1.3             1                        2                             1
 -1              1.4             1                        3                             1
  2               2.5             4                       4                              2
  2               2.6             4                       5                              2
  3               3.5             6                       7                              3
  3               3.6             6                       8                              3
  3               3.8             6                       9                              3
 
so that I can select all the records with 1,2 of expected values.
 
Can some one please help?
 
Thanks.

Tags:
M.Saeed Khurram 544 posts Joined 09/12
06 Feb 2014

Hi,
You can use dense rank function to get this output.
http://www.info.teradata.com/htmlpubs/DB_TTU_14_00/index.html#page/SQL_Reference/B035_1145_111A/User-Defined_Functions.093.48.html
 

Khurram

dnoeth 4628 posts Joined 11/04
07 Feb 2014

And if you're not on TD14.10, yet, you might use a workaround:
Missing Functions: DENSE_RANK

Dieter

You must sign in to leave a comment.