CODEWITHSUNDEEP
sqlteradata
NBC
NBC

Reputation: 1698

Get the number of times a unique value appears in a table

I have a table like so:

Email | Number
a     |  0.5
a     |  1
a     |  1.1
b     |  5
b     |  3.7

I want to preserve the same table layout, but get the instance number of a unique email based on the ranked order of the Number column:

Email | Number | RankOrder
a     |  0.5   |  3
a     |  1     |  2
a     |  1.1   |  1
b     |  7.7   |  1
b     |  6     |  2

Upvotes: 0

Views: 39

Answers (2)

Siyual
Siyual

Reputation: 16917

You can do this with a windowed Row_Number():

Select  Email, Number,
        Row_Number() Over (Partition By Email Order By Number Desc) As RankOrder
From    LikeSo;

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269873

If I understand correctly, you want the row_number() function (this is an ANSI standard function available in most databases):

select email, number,
        row_number() over (partition by email order by number desc) as rankorder
from t;

Upvotes: 2

Related Questions