CODEWITHSUNDEEP
sqlsql-serversql-server-2005datetime
jdee
jdee

Reputation: 12070

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.

Upvotes: 0

Views: 756

Answers (3)

Tom H
Tom H

Reputation: 47464

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

Upvotes: 0

Tomalak
Tomalak

Reputation: 338208

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

Upvotes: 1

Tony Andrews
Tony Andrews

Reputation: 132580

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
  )
)

Upvotes: 0

Related Questions