Select X Most Recent Non-Consecutive Days Worth of Data

Anyone got any insight as to select x number of non-consecutive days worth of data? Dates are standard sql datetime. So for example I'd like to select 5 most recent days worth of data, but there could be many days gap between records, so just selecting records from 5 days ago and more recent will not do.

From stackoverflow

• I don't know the SQL Server syntax, but you need to:

  1) Select the dates (with time component truncated) in descending order

  2) Pick off top 5

  3) Obtain 5th value

  4) Select data where the datetime >= 5th value

  Something like this "pseudo-SQL":

  select *
  from data
  where datetime >=
  ( select top 1 date
    from
    ( select top 5 date from
      ( select truncated(datetime) as date
        from data
        order by truncated(datetime) desc
      )
      order by date
    )
  )
  

  Brian : I have a bad feeling that this mechanism will end up being less efficient that it should be.

  Tony Andrews : Me too - any ideas?

  Tomalak : Have a computed column for the TruncatedDate, and an index on it. Then selecting the DISTINCT TOP 5 ORDER BY should be a snap, and you can use the returned value right away to filter efficiently.

• Following the approach Tony Andrews suggested, here is a way of doing it in T-SQL:

  SELECT
    Value,
    ValueDate
  FROM
    Data
  WHERE
    ValueDate >= 
    (
      SELECT 
        CONVERT(DATETIME, MIN(TruncatedDate))
      FROM 
        (
           SELECT DISTINCT TOP 5 
             CONVERT(VARCHAR, ValueDate, 102) TruncatedDate
           FROM 
             Event
           ORDER BY 
             TruncatedDate DESC
        ) d
    )
  ORDER BY
    ValueDate DESC
  

• This should do it and be reasonably good from a performance standpoint. You didn't mention how to handle ties, so you can add the WITH TIES clause if you need to do that.

  SELECT TOP (@number_to_return)
       *   -- Write out your columns here
  FROM
       dbo.MyTable
  ORDER BY
       MyDateColumn DESC