newbiesqlserver newbiesqlserver - 4 months ago 13
SQL Question

SQL SERVER Time and Row Comparison

I'm new to sql server. Is there a way that I could compare the first 2 rows time? My id is unique so if I add another time I want to the same without changing the value of my query. i want to know the time difference of the last 2 rows btw sorry if my question was not clear.

EXAMPLE TABLE

Answer

You can probably come up with a dozen variations on the solutions below. If you can make sense of them all you will have learned a lot of SQL in the process.

-- scalar subqueries
select datediff(second, max("time"), (select max("time") from T)) as difference
from T
where "time" < (select max("time") from T)

-- correlated subquery
select datediff(
           second,
           (select t2."time" from T as t2 where t2."time" < t."time"),
           t."time") as difference
from T as t
where t.id = (select max(id) from T)

-- self outer joins
select datediff(second, max(t2."time"), max(t."time")) as difference
from T as t left outer join T as t2 on t2."time" < t."time" -- by time

select datediff(second, max(t2."time"), max(t."time")) as difference
from T as t left outer join T as t2 on t2.id = t.id - 1 -- by id

-- cross apply
select datediff(second, max(prev."time"), max(t."time")) as difference
from T as t cross apply
    (select max("time") from T as t2 where t2."time" < t."time") as prev("time")

-- cte with case expression
with max_time("time") as (select max("time") from T)
select min(case when t."time" <> max_time."time"
           then datediff(second, t."time", max_time."time") end) as difference
from T as t cross apply max_time

-- derived table and analytic functions
select difference
from (
    select
        row_number() over (order by "time" desc) as rn,
        datediff(second, lag("time") over (order by "time"), "time") as difference
    from T
) T2
where rn = 1