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: Yesterday @ 2:32 PM
Points: 13,302, Visits: 12,168
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: Yesterday @ 7:44 AM
Points: 12,910, Visits: 32,015
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: Tuesday, August 12, 2014 12:40 PM
Points: 945, Visits: 1,771
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:19 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 2:37 PM
Points: 1,945, Visits: 2,921
Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. If you know how, follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.

Here is my guess at what your picture meant. We do not even know the name of the table

CREATE TABLE Foobar
(foo_id INTEGER NOT NULL PRIMARY KEY,
something_type SMALLINT NOT NULL,
something_condition SMALLINT NOT NULL,
non_relational_status_flag BIT NOT NULL,
non_relational_ordering_flag BIT);

INSERT INTO Foobar
VALUES
(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. <<

'active' is a status value, a state of being. That means it has a duration, a temporal dimension. Where is that in the DDL? This is one of the many ways that RDBMS is not like punch card processing.

>> Out of those 3 conditions, 2 have non_relational_ordering_flag value (1 and 0). <<

The sort order is meta data and should never be in the table. And what does NULL mean in the context of a sorting order?

>> I need to write a script where I need to update the record [sic: rows are not records; punch cards are also called unit records, BTW] where condition is active [unh? The something condition is a numeric value and not a flag in your narrative`] but non_relational_ordering_flag IS NULL (In the case above, it's something_condition = 5).

>> I need to update the non_relational_ordering_flag with the next value of the maximum non_relational_ordering_flag for the type where on_relational_status_flag is 1. <<

That update would be how we set flags when we worked with punch cards. Today, we would use a predicate to test for the current state of the data.

Please start over and give us enough information to get you a workable, properly normalized schema. But you are going to have to learn a lot of new concepts and get rid of the old ones. A SQL is not the best place to get an education.


Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1338846
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: Yesterday @ 2:32 PM
Points: 13,302, Visits: 12,168
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
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse