Maximum value - 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

General SQL Server Forums

New to SQL Server Programming

Maximum value

Author

Topic

njahnavi
Starting Member

4 Posts

Posted - 2009-03-16 : 12:15:43

I have data like this

JObNO
1
2
4
5
3
6
9

I want to write a query like this which gives the jobno and also the difference between the jobno and the next greatest jobno

JObNO Difference
1 1
2 1
4 1
5 1
3 1
6 3
9 0

Any ideas..

Any help is greatly appreciated.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-16 : 12:31:19

[code]
SELECT t.JobNo,
COALESCE((SELECT TOP 1 JobNo FROM Table WHERE JobNo > t.JobNo ORDER BY JobNo),0)- t.JobNo
FROM Table t
[/code]