Ranking values for each date in SQL

I am using SQL Server 2005. Consider the following example:

Table definition and example data

create table my_table
(
  issueid nchar(10) not null,
  date datetime not null,
  rate numeric(18,9),
  rate_roll numeric(18,9)
);

insert into my_table
(issueid, date, rate)
values ('1', '20140131', '0.6'),
('3', '20140131', '0.6'),
('1', '20140228', '0.4'),
('2', '20140228', '0.4'),
('3', '20140228', '0.4'),
('1', '20140331', '0.7'),
('2', '20140331', '0.7'),
('3', '20140331', '0.7'),
('1', '20140430', '0.1'),
('2', '20140430', '0.1'),
('3', '20140430', '0.1');

SqlFiddle

I would like to rank the rate column for each of the dates. I know how to do this for one date:

My query

select issueid, date, rate,
DENSE_RANK() over (order by rate desc) AS Rank
from my_table
where date = '20140131'
and rate is not null
order by Rank

How can I tweak this query so that I can do this for each date?

You are looking for partition by:

select issueid, date, rate,
       DENSE_RANK() over (partition by date order by rate desc) AS Rank
from my_table
where rate is not null
order by date, Rank;

You can readily put this in an update:

with toupdate as (
      select t.*
             DENSE_RANK() over (partition by date order by rate desc) AS DayRank
      from my_table t
      where rate is not null
     )
update toupdate
     set rate_rank = dayrank;