CODEWITHSUNDEEP
mysqlsql
user3697289
user3697289

Reputation: 155

Calculate the distance between following records in SQL

I have a view X that return those records:

ID   Value
1    8
2    20
3    24
4    80

and I need to calculate the distance between each record value and his following. In the last record, (id =4) I should insert default distance of 100. so I did this query:

SELECT a.ID , (a.Value-b.Value) as DistanceToNextRecord
from X as a cross join X as b ON ( a.ID = b.ID-1)

and got :

ID  DistanceToNextRecord
1   12
2   4
3   56

but I don't know how to treat the last record. I need another row :

4   100

how can I do that?

Thank you!!!

Upvotes: 1

Views: 587

Answers (2)

Odhran Hardy
Odhran Hardy

Reputation: 11

Using a left join will allow you to pull back the last row which has no match in your join condition. You can then use ISNULL to default it to 100

I'm using SQL Server but mySQL should be able to do the same thing.

DECLARE @myView TABLE (ID int, VALUE int)

INSERT INTO @myView VALUES  (1,8)
INSERT INTO @myView VALUES  (2,20)
INSERT INTO @myView VALUES  (3,24)
INSERT INTO @myView VALUES  (4,80)

SELECT a.ID, ISNULL((b.VALUE-a.VALUE),100) AS DistanceToNextRecord
FROM @myView AS a LEFT JOIN @myView AS b ON (a.ID = b.ID-1)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269773

I prefer to do this type of calculation using a correlated subquery (unless your database supports lead():

select x.id, x.value,
       (select x2.value
        from x x2
        where x2.id > x.id
        order by x2.id
        limit 1
       ) - x.value as DistanceToNextRecord
from x x;

This will return NULL for the last row.

EDIT:

If you can be sure that the ids are sequential with no gaps, then you can modify your query to use a left outer join:

SELECT a.ID , (a.Value-b.Value) as DistanceToNextRecord
from X a left join
     X b
    ON a.ID = b.ID - 1;

Upvotes: 1

Related Questions