newbiesqlserver - 10 months ago 33

SQL Question

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.

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