conditional re-order of records

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)

conditional re-order of records

Author

Topic

gsnk
Starting Member

24 Posts

Posted - 2002-04-09 : 16:00:05

I've been battling this problem:
A table in our company's db contains records for different users that can be classified in several different ways. I now have to be able to

select *
from table
where userid = @userid
and type = @type
order by priority

and then overwrite the priority field with a simple counter (@cnt = @cnt + 1).

Obviously I'm not able to do this using

update table
set @cnt = priority = @cnt + 1
where <conditions are true>
order by priority

Is there ANY other way that lets me do that nicely?

Thanks so much for the help!

G

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2002-04-09 : 16:03:46

quote:

Obviously I'm not able to do this using

update table
set @cnt = priority = @cnt + 1
where <conditions are true>
order by priority

Have you tried doing it that way?

setBasedIsTheTruepath
<O>

gsnk
Starting Member

24 Posts

Posted - 2002-04-09 : 16:10:33

absolutely... ;) I tried almost anything I could think of. I even created a function returning a table that's already been filtered and put in correct order.

bottom line is: if you update you CANNOT order by... :(

EDIT:

here's how it looked with the function:

update dbo.function(<conditionals>)
set @cnt = priority = @cnt + 1

... did NOT work due to the order statement inside the function

Edited by - gsnk on 04/09/2002 16:11:54

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2002-04-09 : 16:16:07

I think i read your post too quickly and misunderstood you. Are you trying to increment the value of priority in the table?

as in:
update table
set priority = priority + 1

why is the order important? do you mean that you want priority to be some sort of row #?

setBasedIsTheTruepath
<O>

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-04-09 : 16:47:46

Maybe instead of an "order by priority" you can select the MAX(priority) and then add one to that.

Have a look at my example:

quote:

DECLARE @Var1 integer

CREATE TABLE #Answers(UserID uniqueidentifier, answer integer)

INSERT INTO #Answers (userID, answer) Values('994056C0-5B47-4293-94A6-EE80C9B94D34', 1)
INSERT INTO #Answers (userID, answer) Values('B6BE86C6-097E-47BD-B83D-8CECD9F3B4FC', 0)

Set @Var1 = (select max(answer) FROM #answers)

select @Var1 as CurrentMaxAnswer

update #Answers set @Var1 = answer = (select max(answer) + 1 as maxAnswer FROM #answers) where userid='B6BE86C6-097E-47BD-B83D-8CECD9F3B4FC'

select @Var1 as NewMaxAnswer

Drop table #answers

Michael

gsnk
Starting Member

24 Posts

Posted - 2002-04-09 : 16:56:31

yes, I'm trying to simply reset the priority field by an increment value or 1. let me try to re-state the reqs:

1. current records will contain priority values that may not be linear (like: 1, 2, 3, 6, 20, 30 instead of: 1, 2, 3, 4, 5, 6)
2. records will have to be moved up and down along those priority values (ie: need to be able to swap the priority values of two records)
3. in order to facilitate #2 I have to *make sure* that the priority values are linear. otherwise I can't move records around. here's how I'm accomplishing the moving up by swapping the record with the higher priority count with the one to be moved up:


	/*this moves the current record (that is 1 above the one
	that needs to be moved) down 1 count*/
	UPDATE dbo.tbConfig_UserNotificationRules
	SET RulePriority = RulePriority - 1
	WHERE RulePriority = @RulePriority + 1
	  AND UserID = @UserID
	  AND ItemType = @Type
	  AND ActionType = @Action

			SELECT @err = @@ERROR, @rc = @@ROWCOUNT
			IF @err <> 0
			 BEGIN
				RETURN -1
			 END

	/*this now moves the record that we want to move
	 one count up */
	UPDATE dbo.tbConfig_UserNotificationRules
	SET RulePriority = @RulePriority + 1
	WHERE UserNotificationRuleID = @RuleID

			SELECT @err = @@ERROR, @rc = @@ROWCOUNT
			IF @err <> 0
			 BEGIN
				RETURN -1
			 END

Does that make sense? I hope...

Edited by - gsnk on 04/09/2002 17:01:38

gsnk
Starting Member

24 Posts

Posted - 2002-04-09 : 17:00:37

michaelP, that won't work since I will have to access and re-order multiple records... unless I'm missing something here with your answer...

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-04-09 : 17:06:46

Yeah, I didn't see your more detailed (and hella more complicated) question.

Sorry I couldn't help.

Michael

motokevin
Starting Member

36 Posts

Posted - 2002-04-09 : 19:39:59

Try this:

Create Table #TempPriority( NewOrder int Identity(1,1), PriorityKeyColumn int, Priority int)

Insert Into #TempPriority (PriorityKeyColumn, Priority)
Select @PriorityKeyColumn,Priority
From Table
Where <conditions are true>
Order By Priority

Update Table
Set Table.Priority=#TempPriority.NewOrder
From Table,#TempPriority
Where Table.PriorityKeyColumn,#TempPriority.PriorityKeyColumn

Drop Table #TempPriority

This will create a temporary table, order the data correcly, and then update the original table using the correct order.

gsnk
Starting Member

24 Posts

Posted - 2002-04-10 : 02:04:34

motokevin,

you're 'da man!! This works like a charm. WOW!! The idea of a temp table had crossed my mind but I didn't really want to dig into yet another solution - and - voilá - here it is!

Thanks a BIG bundle...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources