Solved

reset sort order

Posted on 2011-03-17
12
344 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Learn by Doing. Anytime. Anywhere.

Do you like to learn by doing?
Our labs and exercises give you the chance to do just that: Learn by performing actions on real environments.

Hands-on, scenario-based labs give you experience on real environments provided by us so you don't have to worry about breaking anything.

Question has a verified solution.

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

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

728 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