difference between 2 rows

[silviadana]

Hello
I need help with two problems in Access. I believe that they are similar but I don't know how it works

I problem: I have a table TELEPHONE(telephone_number) where telephone number is the only field. I have to find the first number that is missing

For example: suppose that I have the entries
1
2
5
7
8
9

The result for the sql that I need would be 3.
I believe that i need to do the difference between two consecutive rows and to find the first row where this difference is greater than 1


The second problem is similar I think: A table Temperatures(day, temperature) and I need to find the bigest difference between two consecutive dates

day temp
1.01 5
2.01 4
3.01 9
4.01 5

and the result would be 5

 

[silviadana]

I would give myself an answer. If you know another methods please post them

For the first problem

Select top 1 s.[telephone_number] + 1 from telephone as s where not exists (Select 1 from telephone as s2 where s2.[telephone_number]= s.[telephone_number]+ 1)


For the second one

SELECT max(abs(x1.temperature-x2.temperature)) AS dif
FROM temperatures AS x1 LEFT JOIN temperatures AS x2 ON x1.day=x2.day+1;

 

[namliam]

I would give myself an answer. If you know another methods please post them

For the first problem

Select top 1 s.[telephone_number] + 1 from telephone as s where not exists (Select 1 from telephone as s2 where s2.[telephone_number]= s.[telephone_number]+ 1)

Well kindoff, except that any "not exists" can be better done with a 'normal' outer join syntax.

For the second one

SELECT max(abs(x1.temperature-x2.temperature)) AS dif
FROM temperatures AS x1 LEFT JOIN temperatures AS x2 ON x1.day=x2.day+1;

exactly, and this kind of outerjoin syntax you can/should use too for your first problem

FYI:
When posting code/sql on the forum plz use the # button to wrap the code and plz do format it too... i.e.

SELECT max(abs(x1.temperature-x2.temperature)) AS dif
FROM temperatures AS x1 
LEFT JOIN temperatures AS x2 ON x1.day=x2.day+1;