Solved

reset sort order

Posted on 2011-03-17
12
340 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
ID: 35160866
Can you post some sample data with expected result.
0
 

Author Comment

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

Author Comment

by:lrbrister
ID: 35189467

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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

Author Comment

by:lrbrister
ID: 35214519
Thanks
0
 
LVL 25

Accepted Solution

by:
jogos earned 500 total points
ID: 35216109
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
 

Author Comment

by:lrbrister
ID: 35216212
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
ID: 35216405
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
ID: 35237221
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
ID: 35240160
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
ID: 35240593
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
ID: 35240767
jogos:
Your suggestion of case when active = ''True'' then new_value else new_value + 1000  was what I needed.

Thanks
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

803 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