How do I write a WHERE clause in SQL Server for the following case?

Question

I have an SQL table with two columns Dateand Userand contains the following rows:

    **Date        User**
    2009-02-20  Danny
    2009-02-20  Matthew
    2009-02-15  William
    2009-02-15  Abner
    2009-12-14  Joseph
    1900-01-01  Adam
    1900-01-01  Eve

Given the date, how do I write a WHERE clause to return a list of users for this date, or if no users were found for this date, return the list of users for the next earlier date. For example, if the specified date is " 2009-02-19 ", the list of users should be William and Abner .

Answer 1

SELECT User
FROM MyTable
WHERE MyDate = (SELECT MAX(MyDate) FROM MyTable WHERE MyDate <= '2009-02-19')

Answer 2

SELECT [User] FROM myTable WHERE Date =
  (SELECT Max(Date) FROM myTable WHERE Date <= '2009-02-19')

Answer 3

SELECT * 
FROM [MyTable] t
INNER JOIN
   ( 
      SELECT TOP 1 [Date]
      FROM [MyTable]
      WHERE [Date] >= @TargetDate
      ORDER BY [Date]
   ) d ON t.[Date] = d.[Date]

Answer 4

- :

select Name
from UnknownTable
where [Date] = 
    (select Max([Date]) 
     from UnknownTable
     where [Date] <= '2009-02-19')

, .

Answer 5

You can do something like this

SELECT TOP 2 [Date] [User] FROM [LearningDatabase]. [Dbo]. [Users] WHERE [Date] = '07 / 15/2009 'OR [Date] <'07 / 15/2009