August 1, 2012 at 2:25 pm
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.
August 1, 2012 at 2:29 pm
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 1, 2012 at 2:40 pm
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.
August 1, 2012 at 2:47 pm
i think it's simply like this:
...ORDER BY ISNULL(SortOrder,2)
Lowell
August 1, 2012 at 3:04 pm
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.
August 1, 2012 at 3:05 pm
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 🙁
August 1, 2012 at 3:06 pm
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 performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
August 1, 2012 at 3:22 pm
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.
August 1, 2012 at 3:25 pm
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 1, 2012 at 3:25 pm
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 performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
August 2, 2012 at 6:42 am
Sean Lange (8/1/2012)
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
Thanks for the code. It works for the sample code. Now, How do we do this for all types. In the sample code, we only have one type which is 860. In my table I have hundreds of types which have similar data error.
For those, who are wondering why this happened in the database, it's because of some DBA diddn't do his job properly six months ago and now, we are finding out that some of the data are not showing up properly on the screen and by tracing back to data, we figured out that those conditions are active but they don't have sortOrder.
Anyway, I really appreciate your help and if you could help me to solve for all types, I would never forgot you 🙂
August 2, 2012 at 7:18 am
You're better off using a CTE than the CROSS APPLY.
WITH ActiveUpdates AS (
SELECT [ID], SortOrder, isActive, ROW_NUMBER() OVER( ORDER BY SortOrder DESC, [ID] ) - 1 AS rn
FROM @CustomerConditions
)
UPDATE ActiveUpdates
SET SortOrder = rn
WHERE isActive = 1
AND SortOrder IS NULL;
Here are the stats.
CTE
Table '#64FCD10B'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
CROSS APPLY
Table '#65F0F544'. Scan count 7, logical reads 11, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 1, logical reads 11, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
As you can see the CTE only scans the table once, but the CROSS APPLY scans the table once for the outer query and once for every single row in the outer query to get the results for the subquery. With such a small table, you won't notice much of a difference, but, as you add rows to the table, the difference will become marked.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 2, 2012 at 7:20 am
kaushal_shah26 (8/2/2012)
Thanks for the code. It works for the sample code. Now, How do we do this for all types. In the sample code, we only have one type which is 860. In my table I have hundreds of types which have similar data error.
You will just need to add a PARTITION clause to the OVER() function to partition on type.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 2, 2012 at 7:49 am
drew.allen (8/2/2012)
You're better off using a CTE than the CROSS APPLY.
WITH ActiveUpdates AS (
SELECT [ID], SortOrder, isActive, ROW_NUMBER() OVER( ORDER BY SortOrder DESC, [ID] ) - 1 AS rn
FROM @CustomerConditions
)
UPDATE ActiveUpdates
SET SortOrder = rn
WHERE isActive = 1
AND SortOrder IS NULL;
Here are the stats.
CTE
Table '#64FCD10B'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
CROSS APPLY
Table '#65F0F544'. Scan count 7, logical reads 11, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 1, logical reads 11, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
As you can see the CTE only scans the table once, but the CROSS APPLY scans the table once for the outer query and once for every single row in the outer query to get the results for the subquery. With such a small table, you won't notice much of a difference, but, as you add rows to the table, the difference will become marked.
Drew
Good call Drew. I tend to forget using a CTE for updates. Will have to remember to keep that in the arsenal.
_______________________________________________________________
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 2, 2012 at 8:51 am
The sample data doesn't contain any holes. Maybe there aren't any. Here's a sample set with a hole and two [Type]'s:
SET NOCOUNT ON
DROP TABLE #CustomerConditions
CREATE TABLE #CustomerConditions(
[Id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
[Type] [int] NULL,
[ConditionId] [int] NULL,
[isActive] [bit] NULL,
[SortOrder] [int] NULL
);
insert into #CustomerConditions (Type,ConditionId,isActive,SortOrder) values (850,2,1,1);
insert into #CustomerConditions (Type,ConditionId,isActive,SortOrder) values (850,3,1,0);
insert into #CustomerConditions (Type,ConditionId,isActive,SortOrder) values (850,5,1,3);
insert into #CustomerConditions (Type,ConditionId,isActive,SortOrder) values (850,6,1,null);
insert into #CustomerConditions (Type,ConditionId,isActive,SortOrder) values (850,1,0,null);
insert into #CustomerConditions (Type,ConditionId,isActive,SortOrder) values (850,4,0,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);
CREATE INDEX ix_Type ON #CustomerConditions ([Type], SortOrder)
SELECT *
FROM #CustomerConditions
ORDER BY [Type], ID
Here's a solution which works if there are holes;
;WITH MyTable AS (
SELECT
rn = ROW_NUMBER() OVER(PARTITION BY c.[Type] ORDER BY c.ConditionId),
t.MAX_SortOrder,
c.*
FROM #CustomerConditions c
INNER JOIN (
SELECT [Type], MAX_SortOrder = MAX(SortOrder)
FROM #CustomerConditions
GROUP BY [Type]
) t ON t.[Type] = c.[Type]
WHERE c.IsActive = 1 AND c.SortOrder IS NULL
) UPDATE MyTable SET SortOrder = rn + MAX_SortOrder
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply