Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Update sortorder column to +1 Expand / Collapse
Author
Message
Posted Wednesday, August 1, 2012 2:25 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, October 30, 2013 2:29 PM
Points: 20, Visits: 30
Hi Guys,
I have a table like this

Id----------Type--------ConditionId-------isActive------SortOrder
1001-------860----------2----------------1------------1
1002-------860----------3----------------1------------0
1003-------860----------5----------------1------------Null
1004-------860----------1----------------0------------Null
1005-------860----------4----------------0------------Null


There are 3 conditions which are active. Out of those 3 conditions, 2 have sortorder value (1 and 0).

I need to write a script whereI need to update the record where condition is active but sortOrder is null (In the case above, it's conditionId 5).
I need to update the sortOrder with the next value of the maximum sortOrder for the type where isactive is 1.

so, in the case above, after the update, Id = 1003 should look lik this

1003-------860----------5----------------1------------2

I have been trying combination of queries but more I tried, more confused I got.

I would appreciate if you guys can guide me to resolve this matter.

Thanks.





Post #1338807
Posted Wednesday, August 1, 2012 2:29 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:48 PM
Points: 14,216, Visits: 13,907
I think I understand what you are trying to do. Can you post some ddl (create table) and sample data (insert statements) in a consumable format?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1338809
Posted Wednesday, August 1, 2012 2:40 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, October 30, 2013 2:29 PM
Points: 20, Visits: 30
Thanks for the quick response. Below is the script that I used to create table and insert sample data.

CREATE TABLE [dbo].[CustomerConditions](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Type] [int] NULL,
[ConditionId] [int] NULL,
[isActive] [bit] NULL,
[SortOrder] [int] NULL
);


insert into CustomerConditions (Type,ConditionId,isActive,SortOrder) values (860,2,1,1);

insert into CustomerConditions (Type,ConditionId,isActive,SortOrder) values (860,3,1,0);

insert into CustomerConditions (Type,ConditionId,isActive,SortOrder) values (860,5,1,null);

insert into CustomerConditions (Type,ConditionId,isActive,SortOrder) values (860,6,1,null);

insert into CustomerConditions (Type,ConditionId,isActive,SortOrder) values (860,1,0,null);

insert into CustomerConditions (Type,ConditionId,isActive,SortOrder) values (860,4,0,null);

select * from CustomerConditions;


-------------

Thanks.
Post #1338817
Posted Wednesday, August 1, 2012 2:47 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:31 PM
Points: 13,559, Visits: 34,476
i think it's simply like this:
...ORDER BY ISNULL(SortOrder,2)



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1338823
Posted Wednesday, August 1, 2012 3:04 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, October 30, 2013 2:29 PM
Points: 20, Visits: 30
kaushal_shah26 (8/1/2012)
Hi Guys,
I have a table like this

Id----------Type--------ConditionId-------isActive------SortOrder
1001-------860----------2----------------1------------1
1002-------860----------3----------------1------------0
1003-------860----------5----------------1------------Null
1004-------860----------1----------------0------------Null
1005-------860----------4----------------0------------Null


There are 3 conditions which are active. Out of those 3 conditions, 2 have sortorder value (1 and 0).

I need to write a script whereI need to update the record where condition is active but sortOrder is null (In the case above, it's conditionId 5).
I need to update the sortOrder with the next value of the maximum sortOrder for the type where isactive is 1.

so, in the case above, after the update, Id = 1003 should look lik this

1003-------860----------5----------------1------------2

I have been trying combination of queries but more I tried, more confused I got.

I would appreciate if you guys can guide me to resolve this matter.

Thanks.







Sorry, I forgot to mention that there might be case where two or more conditions are active but they do not have sort order. In the case above, If I add one more row

1006-------860----------6----------------1------------Null (conditionId = 6)
then I have two conditionId (5 & 6) that are active but sortorder is null

The final output should be

1003-------860----------5----------------1------------2
1006-------860----------6----------------1------------3


Thanks a million.


Post #1338838
Posted Wednesday, August 1, 2012 3:05 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, October 30, 2013 2:29 PM
Points: 20, Visits: 30
Lowell (8/1/2012)
i think it's simply like this:
...ORDER BY ISNULL(SortOrder,2)



yes it's simple if I have only one record to update but unfortunately I have thousands records like this
Post #1338840
Posted Wednesday, August 1, 2012 3:06 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, April 17, 2015 2:35 PM
Points: 887, Visits: 1,781
i have a couple of things in mind but i think the OP is looking for an UPDATE statement. the question i have is that i cant see why condition ID 5 should have sort order 2. as condition id 3 has sort order 0 and condition id 2 has sort order 1. Also if condition ID 1 becomes active does the SortOrder have to change to reflect that or does it just get the next higher SortOrder.

how sortorder and the ID and condition ID relate will be the determining factor on which UPDATE statement is going to yield the correct results.



For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw

Need to Split some strings? Jeff Moden's DelimitedSplit8K
Jeff Moden's Cross tab and Pivots Part 1
Jeff Moden's Cross tab and Pivots Part 2

Jeremy Oursler
Post #1338841
Posted Wednesday, August 1, 2012 3:22 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, October 30, 2013 2:29 PM
Points: 20, Visits: 30
capn.hector (8/1/2012)
i have a couple of things in mind but i think the OP is looking for an UPDATE statement. the question i have is that i cant see why condition ID 5 should have sort order 2. as condition id 3 has sort order 0 and condition id 2 has sort order 1. Also if condition ID 1 becomes active does the SortOrder have to change to reflect that or does it just get the next higher SortOrder.

how sortorder and the ID and condition ID relate will be the determining factor on which UPDATE statement is going to yield the correct results.


If inactive condition ever become active then we have other mechanisms to assign sortorder, so no worries for inactive conditions.

Thanks.
Post #1338849
Posted Wednesday, August 1, 2012 3:25 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:48 PM
Points: 14,216, Visits: 13,907
This should work with your sample data.

select *
from CustomerConditions
where IsActive = 1
order by SortOrder

begin transaction

update CustomerConditions
set SortOrder = x.RowNum
from CustomerConditions c
cross apply
(
select ConditionID, ROW_NUMBER() over (order by isnull(SortOrder, 9999999), ConditionID) - 1 as RowNum
from CustomerConditions
where IsActive = 1
) x
where x.ConditionID = c.ConditionID

select *
from CustomerConditions
where IsActive = 1
order by SortOrder

rollback transaction



_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1338852
Posted Wednesday, August 1, 2012 3:25 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, April 17, 2015 2:35 PM
Points: 887, Visits: 1,781
kaushal_shah26 (8/1/2012)
capn.hector (8/1/2012)
i have a couple of things in mind but i think the OP is looking for an UPDATE statement. the question i have is that i cant see why condition ID 5 should have sort order 2. as condition id 3 has sort order 0 and condition id 2 has sort order 1. Also if condition ID 1 becomes active does the SortOrder have to change to reflect that or does it just get the next higher SortOrder.

how sortorder and the ID and condition ID relate will be the determining factor on which UPDATE statement is going to yield the correct results.


If inactive condition ever become active then we have other mechanisms to assign sortorder, so no worries for inactive conditions.

Thanks.


ok so that answers one question. the other question is how sort order relates to any thing else. is there any reason to the sort order ie. Is there a reason conditionID 3 has a sort order of 0 and conditionID 2 has sortorder of 1?



For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw

Need to Split some strings? Jeff Moden's DelimitedSplit8K
Jeff Moden's Cross tab and Pivots Part 1
Jeff Moden's Cross tab and Pivots Part 2

Jeremy Oursler
Post #1338853
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse