Incrementing from a SELECT TOP

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

SQL Server Development (2000)

Incrementing from a SELECT TOP

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-03-04 : 21:32:41

Tony writes "I know it's possible to SELECT TOP 10 etc ... However, how do I SELECT TOP 1 fieldName FROM table1 ORDER DESC and then add 1 to it so that I can then input the new number for the next in line. I can't have the field be a GUID, as I will need to reorder the numbers as well with a basic move up and move down function which I already have written. So far, I have this:

SELECT TOP 1 fieldName FROM table1 ORDER BY DESC;
Dim oldNumber
oldNumber = RS.Fields("fieldName")
Dim newNumber
newNumber = oldNumber + 1

How far off base am I?

Thanks!
Tony"

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-03-04 : 22:24:43

Sorry Tony, but perhaps you could give us some more info as to why you are trying to do this?

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

amoneemus
Starting Member

1 Post

Posted - 2002-03-04 : 23:36:06

I have a table with say, 10 fields. One field may be "OrderNumber". If the item is archived, the "OrderNumber" field is "0". Else, there is an order number. However, I can't have the order number skipping numbers because I need to be able to change the order via an "Up" or "Down" link.

For Example - to insert a new record and give it the next "OrderNumber" number:

"SELECT TOP 1 Order FROM table1 WHERE OrderNumber <> '0' ORDER BY OrderNumber DESC;"

This gives me the highest number there is. Now, I need to increment this number by 1:

Dim intOldNumber
Dim intNewNumber
intOldNumber = objRS.Fields("OrderNumber")
intNewNumber = intOldNumber + 1

Now, when I do my INSERT for my new entry, I have intNewNumber as my next "OrderNumber" number.

However, this is where my problem starts. Let's say I go back and "Archive" a previous item. Now, I have a space between two of the numbers.

First, I need to be able to ensure that there is no space if I "Archive" an old item.

If my database set up is:

UID, OrderNumber, Title, Comments
1 - 1 - First - First one(or whatever)
2 - 2 - Second - Second one
3 - 3 - Third - Third One
4 - 4 - Fourth - Fourth One

In the above example, if I "Archive" UID 2, then there will be a space between 1 and 3. How do I go about changing all the "OrderNumber" numbers higher than the archived number so that they are one less than they were? I need "OrderNumber" 3 to become 2 and "OrderNumber" 4 to become 3.

Enough Info?

Thanks,
Tony

Me? Wrong? Don't tell my wife.

Nazim
A custom title

1408 Posts

Posted - 2002-03-04 : 23:47:10

Am not sure i understand your question fully.
thought this link might help you http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=11716.

if it doesnt. post some sample data and expected results.

--------------------------------------------------------------

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-03-05 : 00:38:21

Similarly - I'm still missing something.

Each time one of your items gets archived, and you need to renumber all the higher rows, you can do something like :

declare @intCounter int
set @intCounter = 0
update table1
SET @intCounter = OrderNumber = @intCounter + 1

here's another link to add to your list
[url]http://www.sqlteam.com/item.asp?ItemID=765[/url]

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources