Select two consecutive records by date, records from the database, with a maximum difference of dates

I want to select two consecutive records by date, records from the database, with a maximum difference in consecutive dates.

there is a table

name date
abc  1242
bcd  1246
bsd  1247
bse  1249

The result of the query should be:

abc  1242
bcd  1246

My query is:

    select t1.name, t2.name, max(t2.date - t1.date)
    from temp t1
    join temp t2 on t1.date < t2.date
    where
    not exists (
        select t3.date from temp t3 where t3.date > t1.date and t3.date < t2.date
    )

Is this the best solution?

Thanks in advance

Answers


declare @a table(name varchar(10), date int)
insert into @a Select 'abc',  1242
union Select 'bcd',  1246
union Select 'bsd',  1280
union Select 'bse',  1242

Select row_number() OVER (ORDER BY name) AS 'RowNumber',*  into #temp from @a

select Top 1 * into #tbl from
(
select a1.Name as N1,a2.Name as N2,abs(a2.date-a1.date) as diff
 from #temp a1 Join #temp a2 on a2.rownumber-1  = a1.rownumber
 )as tbl order by diff desc

select * from @a where name =(select N1 from #tbl) or name =(select N2 from #tbl)

Drop table #temp
Drop table #tbl

My solution, good for SQL 2012 at least!

declare @data table (name varchar(10), date int)

-- Your test data
insert into @data (name, date) values ('abc', 1242),('bcd',  1246),('bsd',  1247),('bse',  1249)

SELECT d.name, d.date FROM (
  SELECT TOP 1 name, prevName FROM (
    SELECT name, prevName, date-prevDate as datePrevDiff FROM (
      SELECT name, date
        , MAX(d.name) OVER (ORDER BY d.date ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) as prevName
        , MAX(d.date) OVER (ORDER BY d.date ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) as prevDate
      FROM @data d
    ) dataWithPrev
  ) dataWithPrevDiff
  ORDER BY datePrevDiff DESC
) largestDiff
INNER JOIN @data d ON d.name in (largestDiff.name, largestDiff.prevName)

This assumes sorting by date is the right thing to do...


Need Your Help

Conditional Validation on Different Pages - Rails

ruby-on-rails ruby ruby-on-rails-3

A quick Rails question that I would love your guys' help on. Been looking around for the answer and everything out there seems a bit on the more complex side for what I think is the solution. I hav...

How to use the XPath contains() function within document.evaluate()?

xml dom xpath document.evaluate

I have this section of code which I've been using to filter out fields based on a searched name. The problem is that I need it to be a little forgiving in terms of what the user can type. So I have...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.