Is it possible to resort/update the column based

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 2008 Forums

Transact-SQL (2008)

Is it possible to resort/update the column based

Author

Topic

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2011-05-03 : 09:49:51

Is it possible to resort/update the column based on modid and duedate

table name is TableActivities.

ActivityID modid priority duedate
---------------------------------------------------
23 12 1 05/12/2011
34 12 5 05/23/2011
63 12 5 05/23/2011
77 12 5 05/23/2011
89 12 5 05/23/2011
90 12 6 05/31/2011
112 12 7 06/07/2011

Is it possible to resort/update the Priority column based on "modid" and "duedate"

duedate is date and time datatype, may be should use just "mm/dd/yyyy" excluding time. that way all same date rows will get same priority#.

The priority i have is 1,2,6,7

Instead is it possible to make all 5's to 2?

and then 6 to 3?

and then 7 to 4?

Activityid is primary key(identity column)

based on modid and duedate?

Thank you very much for the helpful info.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-05-03 : 09:54:36

Very little idea of what you are getting at

Instead is it possible to make all 5's to 2?
and then 6 to 3?
and then 7 to 4?

update tbl
set priority = case priority when 5 then 2 when 6 then 3 when 7 then 4 end
where priority in (5,6,7)

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2011-05-03 : 10:07:35

Nigel, Thanks.

I have almost 8000 rows, i was just giving an example of one modid's rows in that table.

I need the query to scan table rows and check where it needs resorting there is it possible to do an update of priority column?

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2011-05-03 : 10:11:21

Sorry, i did'nt realize sample table data got messed above.
here again formatted properly.

table name is TableActivities.


ActivityID	modid	priority  	duedate
---------------------------------------------------
23 ------------	12 -----1 -------------	05/12/2011
34 ------------	12 -----5 -------------	05/23/2011
63 ------------	12 -----5 -------------	05/23/2011
77 ------------	12 -----5 -------------	05/23/2011
89 ------------	12 -----5 -------------	05/23/2011
90 ------------	12 -----6 -------------	05/31/2011
112 -----------	12 -----7 -------------	06/07/2011

Is it possible to resort/update the Priority column based on "modid" and "duedate"

duedate is date and time datatype, may be should use just "mm/dd/yyyy" excluding time. that way all same date rows will get same priority#.

The priority i have is 1,2,6,7

Instead is it possible to make all 5's to 2?

and then 6 to 3?

and then 7 to 4?

Activityid is primary key(identity column)

based on modid and duedate?

Thank you very much for the helpful info.
[/quote]

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-05-03 : 10:38:19

do you mean aresult like this?

declare @Sample table (ActivityID int, modid int, priority int, duedate datetime)
insert @Sample
select 23, 12,1,'20110512' union all
select 34, 12,5,'20110523' union all
select 63, 12,5,'20110523' union all
select 77, 12,5,'20110523' union all
select 89, 12,5,'20110523' union all
select 90, 12,6,'20110531' union all
select 112,12,7,'20110607'

select
dense_rank() over (partition by modid order by priority asc) as new_prio,* from @Sample

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-05-03 : 10:57:58

If the result of my previous post is ok then

update a
set priority = dt.new_prio
from TableActivities a
join
(select dense_rank() over (partition by modid order by priority asc) as new_prio,* from TableActivities)dt
on dt.ActivityID = a.ActivityID

No, you're never too old to Yak'n'Roll if you're too young to die.

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2011-05-03 : 10:59:59

WEbFred Thankss...

Yes exactly, the new priority looks good, how can i use it within update statement, to update the whole table. i have almost 8000 rows.

Thank you very much for the helpful info.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-05-03 : 11:01:11

see above

No, you're never too old to Yak'n'Roll if you're too young to die.

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2011-05-03 : 11:21:25

WEBFRED...... THANK YOU VERY MUCH FOR THE HELP.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources