?
Solved

reset sort order

Posted on 2011-03-17
12
Medium Priority
?
349 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
Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

 

Author Comment

by:lrbrister
ID: 35214519
Thanks
0
 
LVL 25

Accepted Solution

by:
jogos earned 2000 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

800 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