SQL query to list the latest rows for every week

Question

In SQL Server, I have the following data and I want to get the latest row for every week.

I have the following table. The WW (week number of the date) column doesn't exist, just the date, I put it there to explain better the purpose of the query.

Number  Date          WW
392     2012-07-23    30
439     2012-07-24    30
735     2012-07-25    30
882     2012-07-26    30 *
193     2012-07-30    31
412     2012-07-31    31   
425     2012-08-01    31
748     2012-08-02    31
711     2012-08-03    31 *
757     2012-08-07    32
113     2012-08-08    32 *
444     2012-08-15    33 *

The desired result of the query should be

882     30 
711     31 
113     32 
444     33 

Basically what I want to get is the latest row of every week. I have found examples like this Get the records of last month in SQL server to find just the latest one, but I don't know how to extend them to get the list of results for every week. The rows can be stored in any date, for example maybe in a week there are no results, or in a week there are 5 rows and the next week 10 rows. I would really appreciate some hints for this.

Answer 1

You should be able to use something like this:

select t1.number, t2.ww
from yourtable t1
inner join
(
    select max(date) mxdate, datepart(week, [Date]) ww
    from yourtable 
    group by datepart(week, [Date])
) t2
    on t1.date = t2.mxdate
    and datepart(week, t1.[Date]) = t2.ww;

Or you can use CTE:

;with cte as
(
  select number, datepart(week, [Date]) ww,
    row_number() over(partition by datepart(week, [Date]) 
                      order by date desc) rn
  from yourtable
)
select number, ww
from cte
where rn = 1

See SQL Fiddle with Demo

If you use the MAX() version of this and you have two numbers with the same date, then you will return two records. Using the row_number() version will only return one record that meets the criteria due to applying the row_number filter (See Demo)