Solved

reset sort order

Posted on 2011-03-17
12
342 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
  • 2
12 Comments
 
LVL 41

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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 

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 41

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

MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Powershell help for creating accounts 283 55
Need SSIS project 2 29
Split string into 3 separate fields 5 22
Applying Roles in Common Scenarios 3 19
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

733 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