Find closest last day of the week

I have rows with a date column. I need to find the values closest to the end of each week.

Example: For week 3 - 9 Jan, from the values 4,5,6 Jan, it will return 6 Jan, and for week 10 - 16 Jan, from the values 10,11 it will return 11 Jan.

So out of the rows containing 开发者_运维百科4,5,6,10,11 Jan, the query should return Thu 6 and Tue 11 Jan.

Hope this makes sense.

You need to state which RDBMS you are working with for a better answer.

For SQL Server, you can use this

select MAX(date)
from #tmp
group by DATEPART(wk, date)

Note: the "week" runs from Sun-Sat unless you use SET DATEFIRST to change the first day of week.

To run Mon-Sun, use this instead

select MAX(date)
from #tmp
group by DATEPART(wk, date-1)

Test table used

create table #tmp (date datetime)
insert #tmp select '20110104'
insert #tmp select '20110105'
insert #tmp select '20110106'
insert #tmp select '20110110'
insert #tmp select '20110111'