How to obtain the "largest day" in a week?

Question

How to get a data when the "largest day" of the week does not exist?

For example, if Friday does not exist in my database for a particular week (assuming that Saturday and Sundays are not in my database), I would still like to be able to get the data from Thursday. If Both Friday and Thursday do not exist, I would like to get the data from Wednesday etc.

This is what I currently have (this code allows me to obtain the last day of the month from my database):

/**Select Last Day Of Month**/
SELECT * FROM [mytable]
WHERE [date] IN (SELECT MAX([date])
FROM [mytable]
GROUP BY MONTH([Date]), YEAR([Date]))    

I also understand that you can use the DATEPART function to get all datas from a particular day (i.e. Friday), the thing I'm not sure about is how to get a thursday if Friday doesn't exist. I'm looking to grab all the data that has the corresponding features, not one particular week. Thanks!

As a clearer example: Say I have four input data in my database->
1. 2016/8/19(Fri), red
2. 2016/8/18(Thu), blue
3. 2016/8/11(Thu), red
4. 2016/8/10(Wed), red
after the query is executed, I would like to have:
1. 2016/8/19(Fri), red
3. 2016/8/11(Thu), red
They are selected because the two data are the corresponding "largest" data in that week.

Answer 1

See if the following query helps. It displays the last day of every week in the given input data.

declare @table table(
date datetime
)

insert into @table
values
('08/01/2016'),
('08/02/2016'),
('08/03/2016'),
('08/04/2016'),
('08/05/2016'),
('08/06/2016'),
('08/07/2016'),
('08/08/2016'),
('08/09/2016'),
('08/10/2016'),
('08/11/2016'),
('08/12/2016'),
('08/13/2016'),
('08/14/2016'),
('08/15/2016'),
('08/16/2016'),
('08/17/2016'),
('08/18/2016'),
('08/19/2016'),
('08/20/2016'),
('08/21/2016'),
('08/22/2016'),
('08/23/2016')


;with cte as
(
select date, row_number() over(partition by datepart(year,date),datepart(week,date) order by date desc) as rn from @table
)

select date,datename(weekday,date) from cte
where rn = 1

Answer 2

You can extract various weekparts and construct a suitable ROW_NUMBER() expression.

E.g. the following assigns the row number 1 to the latest day within each week:

ROW_NUMBER() OVER (
PARTITION BY DATEPART(year,[date]),DATEPART(week,[date])
ORDER BY DATEPART(weekday,[date]) DESC) as rn