Assigned unique ID to a group of same values

Question

Ok so I have a group of information like this:

2014-01 2014-1-1
2014-01 2014-1-2
2014-01 2014-1-3
2014-02 2014-2-1
2014-02 2014-2-2

I want to create another column so it will look like this: (order)

1     2014-01 2014-1-1
1     2014-01 2014-1-2
1     2014-01 2014-1-3
2     2014-02 2014-2-1
2     2014-02 2014-2-2

I tried the rank function but it didnt' help, only give me 1,2,3,4,5 value throughout

1     2014-01 2014-1-1
2     2014-01 2014-1-2
3     2014-01 2014-1-3
4     2014-02 2014-2-1
5     2014-02 2014-2-2

Anyone please help...?thanks

Answer 1

You should show the query that you tried. In any case, you want dense_rank():

select dense_rank() over (order by col1) , col1, col2
from table t;

If those two values are actually in one column, then do:

select dense_rank() over (order by left(col, 7)), col
from table t;