Assigning unique value for each month

[nelco]

Hi,
I have a table which is as follows:

Month, Description, Language, Total
2009-01, Median Deposit, English, 2081.16
2009-01, Average Deposits, English, 191996.25
2009-01, Subsequent Deposits, Chinese, 2000

and so on for every month

I want to assign a unique value to each month, something like:

Month, Description, Language, Total, Rank
2009-01, Median Deposit, English, 2081.16, 1
2009-01, Average Deposits, English, 191996.25, 1
2009-01, Subsequent Deposits, Chinese, 2000, 1
2008-12, Median Deposit, English, 2081.16, 2
2008-12, Average Deposits, English, 191996.25, 2
2008-12, Subsequent Deposits, Chinese, 2000, 2

Any idea on how this can be done?

 

[gmmastros]

What version of SQL Server are you using?


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[nelco]

SQL Server 2005

 

[fxthoth]

In SQL server 2005 you can use DENSE_RANK(), which should do exactly what you want.

declare @myTable table(
	start datetime,
	info varchar(20)
)

insert into @myTable values ('20080501','Median deposit')
insert into @myTable values ('20080501','Average deposits')
insert into @myTable values ('20080501','Subsequent Deposits')
insert into @myTable values ('20080401','Median deposit')
insert into @myTable values ('20080401','Average deposits')
insert into @myTable values ('20080401','Subsequent Deposits')
insert into @myTable values ('20080301','Median deposit')
insert into @myTable values ('20080301','Average deposits')
insert into @myTable values ('20080301','Subsequent Deposits')

select DENSE_RANK() over (order by start desc), start, info
	from @myTable

I hope I will not bother George with overhaul this answer

 

[gmmastros]

I hope I will not bother George with overhaul this answer

To tell the truth, I was trying to get this to work (and was having problems). Thanks for posting.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[nelco]

Thanks a lot 'fxthoth' for your response. It worked.