Solved

reset sort order

Posted on 2011-03-17
12
338 Views
Last Modified: 2012-05-11
My attached code doesn't quite work. I'm wanting to reset the sort order of ALL sorts...and put the inactive records at the bottom with a sort number = 10001, 1002, 1003 etc ...

Currently...On tables where the sort order is not null AND active = 'False'
It can have a sort order the same as an active sort order, and I wind up with the sort order jumping around.
declare @sql nvarchar(max) 

If @id is null
	Begin
		set @sql = 'WITH UpdateMyTable
							AS
							(SELECT sort, ROW_NUMBER() 
							OVER (ORDER BY sort, id) AS new_value
							FROM ' + @tbl + ' where sort is not null and active = ''True'')

							UPDATE UpdateMyTable
							SET sort = new_value'
	End
Else
	Begin
	set @sql = 'WITH UpdateMyTable
					AS
					(SELECT sort, ROW_NUMBER() 
					OVER (ORDER BY sort, ' + @id + ') AS new_value
					FROM ' + @tbl + ' where sort is not null and active = ''True'')

					UPDATE UpdateMyTable
					SET sort = new_value'
	End	
--Print @sql  
exec (@sql)

Open in new window

0
Comment
Question by:lrbrister
  • 6
  • 3
  • 2
12 Comments
 
LVL 40

Expert Comment

by:Sharath
Comment Utility
Can you post some sample data with expected result.
0
 

Author Comment

by:lrbrister
Comment Utility
It'll have to be in the morning when I return to work
0
 

Author Comment

by:lrbrister
Comment Utility

Here you go

id     text     sort       active
1     Text1    1            True
2      Text2   1             False
3     Text3    1             True
4     Text4    5             True


Desired results
id     text     sort       active
1     Text1    1            True
2      Text2   1             False
3     Text3    2             True
4     Text4   3             True
0
 

Author Comment

by:lrbrister
Comment Utility
Thanks
0
 
LVL 25

Accepted Solution

by:
jogos earned 500 total points
Comment Utility
Q1
don't get your desired result , where is the bottom-order for the non-active?
=>  with something like this you can make that happen
 case when active = ''True'' then new_value else new_value + 1000 end

Q2 the ELSE-part
you introduce the @id-variable in place off the id-column, so when sort order is the same for each record found the @id-value is also the same so it becomse unpredictable


0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:lrbrister
Comment Utility
jogos:
Lets say I just want to reset the sort order and I don't care if it's active or not and not allow any duplicate sort numbers.
0
 
LVL 25

Expert Comment

by:jogos
Comment Utility
so the this is not wanted: and active = ''True''

But what isn't working?
As I suggested there is something with the @id in the ELSE-part
0
 
LVL 40

Expert Comment

by:Sharath
Comment Utility
Is this what you are looking for?
declare @table table (id int,    [text] varchar(10),     sort int,       active varchar(10))
insert @table values (1,     'Text1',    1,             'True')
insert @table values (2,      'Text2',   1,            'False')
insert @table values (3,     'Text3' ,   1,            'True')
insert @table values (4,     'Text4' ,   5,            'True')

select * from @table
/* Before:
id	text	sort	active
1	Text1	1	True
2	Text2	1	False
3	Text3	1	True
4	Text4	5	True
*/
;with cte as (select *,ROW_NUMBER() over (partition by active order by id) rn from @table) 
update cte set sort = rn
select * from @table
/* After
id	text	sort	active
1	Text1	1	True
2	Text2	1	False
3	Text3	2	True
4	Text4	3	True
*/

Open in new window

0
 
LVL 25

Expert Comment

by:jogos
Comment Utility
Difference between sollution of Sarath 123 and my suggestion of just dropping the 'and active =...'
- mine would not have duplicate sort-orders, sarath's no duplicate within the same active-value
  your "order and I don't care if it's active or not and not allow any duplicate sort numbers. "  does not match with your  own example so its difficult for us to know

- sarath's sollution looses the original sort by only ordering on id, so you cannot ameliorate the order

Still waiting on question what is not working
0
 

Author Comment

by:lrbrister
Comment Utility
jogos:
Sorry for not getting back sooner.  Had knee surgery and was away a couple of days.
I am reviewing now and will reply shortly
0
 

Author Closing Comment

by:lrbrister
Comment Utility
jogos:
Your suggestion of case when active = ''True'' then new_value else new_value + 1000  was what I needed.

Thanks
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how the fundamental information of how to create a table.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now