difference between 2 rows - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

difference between 2 rows

Author

Topic

silviadana
Starting Member

2 Posts

Posted - 2010-03-09 : 15:50:37

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
Starting Member

2 Posts

Posted - 2010-03-09 : 20:14:53

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;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources