Sql Server 2005: Today's random records

Question

I can easily get a random record with this:

SELECT *    FROM MyTable    ORDER BY NewId() 

I can easily get a record with "today's date" with this:

SELECT *    FROM MyTable    WHERE MyDate = "2010-24-08"  -- db doesn't store times

But how would I combind the two?

Get 1 random record... anything with today's date.

If none are found... get 1 random record from yesterday (today-1).

If none are found... get 1 random record from etc, etc, today-2

... until 1 record is found.

Answer 1

Use the TOP operator:

  SELECT TOP 1 *    
    FROM MyTable    
   WHERE MyDate = "2010-24-08" 
ORDER BY NEWID() 

...combined with the ORDER BY NEWID(). Without the ORDER BY, you'd get the first inserted row/record of the records returned by the filteration in most cases typically, but the only way to ensure order is with an ORDER BY clause.

SQL Server 2005+ supports brackets on the TOP value, so you can use a variable in the brackets without needing to use dynamic SQL.

Answer 2

Just make the day date the primary order by condition:

select top(1) *
from Table
order by Date desc, newid();

If you store the dates as full day and time, you need to round them out to the day part only: cast (Date as DATE) in SQL 2008 or cast(floor(cast(Date as FLOAT)) as DATETIME) in pre-2008.

Answer 3

You can order them first by date descending... so all of today's will come first, yesterdays next, etc. THEN randomize them within their dates by NewID.

SELECT TOP 1 *
FROM   MyTable
WHERE  MyDate <= GETDATE()
ORDER BY MyDate DESC, NewId() ASC

Where clause optional... in case you actually have future dates in your data set.

Answer 4

Does this give you what you want?

SELECT TOP 1 *
FROM MyTable
ORDER BY MyDate desc, NewId()

This assumes there are no dates later than today.