select 10 rows with lowest time difference in sql

Question

I'm using sql-server 2005

Hi, i have Users table with userID and registrationDate. I want to select shortest period of time between two registrationDates when first date is x and other row is x+10 rows. I don't mind cursor because i will run this query once in a while.

I will explain again, i need shortest period of time between 10 users registrations to get an idea what a high border of registrations per certain time can be.

thanks

Answer 1

Try this query if you are using SQL Server 2005 or newer:

WITH T1 AS (
    SELECT
        userID,
        registrationDate,
        ROW_NUMBER() OVER (ORDER BY registrationDate) AS rn
    FROM Users
), T3 AS (
    SELECT
        T1.registrationDate AS interval_start,
        T2.registrationDate AS interval_end,
        T1.registrationDate - T2.registrationDate AS diff
    FROM T1
    JOIN T1 T2
   ON T1.rn = T2.rn + 5
)
SELECT TOP 1 interval_start, interval_end
FROM T3
ORDER BY diff