SQL query to display the last rows for each week

Question

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

I have the following table. The WW column (week number) does not exist, just the date, I put it there to better explain the purpose of the request.

 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 query result should be

 882 30 711 31 113 32 444 33 

Basically, I want to get the last row every week. I found such examples Get records for the last month on the SQL server to find only the last, but I don’t know how to extend them to get a list of results for each week. Lines can be saved on any date, for example, maybe there are no results in a week, or in a week there are 5 lines, and next week - 10 lines. I would really appreciate it.

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, and you have two numbers with the same date, you will return two entries. Using the row_number() version will only return one record matching the criteria, due to the row_number filter being applied (see Demo )

Answer 2

If you are using sql server, you can do this:

 declare @test table(Number int, Date datetime, WW int); INSERT INTO @test (Number, Date, WW) VALUES (392, '2012-07-22 17:00:00', 30), (439, '2012-07-23 17:00:00', 30), (735, '2012-07-24 17:00:00', 30), (882, '2012-07-25 17:00:00', 30), (193, '2012-07-29 17:00:00', 31), (412, '2012-07-30 17:00:00', 31), (425, '2012-07-31 17:00:00', 31), (748, '2012-08-01 17:00:00', 31), (711, '2012-08-05 17:00:00', 31), (757, '2012-08-06 17:00:00', 32), (113, '2012-08-07 17:00:00', 32), (444, '2012-08-14 17:00:00', 33) SELECT * FROM ( select number, date, ww, row_number() over (partition by ww order by date desc) rn from @test) v WHERE rn = 1; 

Answer 3

This can be done using a correlated subquery, as shown below:

 SELECT number, [date] FROM tablename T WHERE NOT EXISTS ( SELECT 1 FROM tablename WHERE DATEPART(wk, [date]) = DATEPART(wk, T.[date]) AND date > T.[date] ) 

Please note that this does not return 2012-08-06, as I believe it is valid for the 32nd week of the year http://www.wolframalpha.com/input/?i=6th+August+2012