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
 SQL Server 2000 Forums
 Transact-SQL (2000)
 SELECTing the next record in the same query

Author  Topic 

bluemetal
Starting Member

26 Posts

Posted - 2005-06-13 : 21:23:15

Hi,
Have a small question:

I have a table which has records with version numbers running (1 record per version). I wanted to do a single query, that would give me a tuple of the present version AND one extra column stating the next version number (the version numbers are not in sequence, next version could be 2,3,5,9 etc depending on the next record)

Any ideas? Thanks!

SamC
White Water Yakist

3467 Posts

Posted - 2005-06-13 : 21:32:46
There is no "get next record" modifier in T-SQL, so it isn't straightforward.

This is probably one of the most frequently asked questions. Right up there with "How do I delete duplicate records?"

Try searching for "next record" using the "Search" link above and you'll find other similar threads.

Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-06-14 : 02:08:55
You need to give more details
See this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

KLang23
Posting Yak Master

115 Posts

Posted - 2005-06-14 : 14:31:58
Hi,

If the rows are uniquely identified by an identity (or other sequential "key") meaning that the version is just another data column, and by "next version" you mean the version data from the row with the next sequential id then:


Set NoCount ON
Create table MyTable (Id int identity, Version int)

Insert Into MyTable (Version) values (1)
Insert Into MyTable (Version) values (5)
Insert Into MyTable (Version) values (3)
Insert Into MyTable (Version) values (7)
Insert Into MyTable (Version) values (5)

Select A.Id, A.Version
,(Select Top 1 B.Version From MyTable B
Where B.Id > A.Id
order By B.Id) as Next_Version
From MyTable A

Drop table MyTable


Returns:

Id Version Next_Version
----------- ----------- ------------
1 1 5
2 5 3
3 3 7
4 7 5
5 5 NULL


I hope I interpreted your question correctly, and I hope this helps.
Go to Top of Page
   

- Advertisement -