SQL query to get DateDiff of last two records

Question

I have an Event table with eventNum as the primary key and a date as datetime2 (7) in SQL Server 2008 R2. I am trying to get the date of the last two rows in a table and get the difference in minutes. This is what I have now:

Select DATEDIFF(MI, e.date,(Select e2.date from Event e2 where eventNum = (Select MAX(e2.eventNum)))) From Event e Where eventNum = (Select MAX(e.eventNum)-1 from e) 

and I get this error:

Invalid column name 'Select eventNum from Event Where eventNum = Select MAX (eventNum) from Event'.

I changed it 100 times and can't make it work. Any help?

Answer 1

You can use ROW_NUMBER

 WITH CTE AS ( SELECT RN = ROW_NUMBER() OVER (ORDER BY eventNum DESC) , date FROM Event ) SELECT Minutes = DATEDIFF(minute, (SELECT date FROM CTE WHERE RN = 2), (SELECT date FROM CTE WHERE RN = 1)) 

Fiddle: http://www.sqlfiddle.com/#!3/3e9c8/17/0

Answer 2

This should not go through the table twice, as Tim’s answer.

 select datediff(mi, min(x.date), max(x.date)) from ( select top(2) * from Event e order by eventNum desc ) x 

Assuming that you always have 2 records or more, and the time increases monotonously, it works higher.

• If it has only one record, it returns 0 (since max = min = a special record).
• If your times are not monotonously increasing, there is a simple setting for this request

eg.

 select top(1) datediff(mi, x.date, y.date) from event x join event y on y.eventnum < x.eventnum order by x.eventnum desc, y.eventnum desc