Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Update in SQL Server
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

1 Posts

Posted - 04/30/2013 :  05:25:57  Show Profile  Reply with Quote
I have a requirement in SQL Server where in one of the DB tables i need to group the data on the basis of a Grouping flag. And this GroupingFlag is generated on the 0 value occurence of another column in this table called 'Price'. For EX, plz refer below table

Product Price GroupingFlag
P1 -10 1
P1 -10 2
P2 10 3
P2 20 4
P2 0 4
P3 20 5
P3 -15 6
P3 0 6
P3 0 6
P3 0 6
Now, on the basis of 0 occurrence in Price value, Groupingflag value is set. first row of price is -10, hence Groupingflag is marked as 1, in second row again this is non zero, hence Grouping flag is marked as 2. This goes on till fourth row, where 0 appears now, Groupingflag should retain the previous occurrence of GroupingFlag value i.e.4.

SO I need to populate this GroupingFlag column in a table which has huge data. I have achieved this with the help of While/For Loop but these are taking considerable amount of item. Is there any approach where we can do this, by single update statement or select statement. I have tried to implement it using below Update query, however with huge data this is giving abrupt results but working for less data.

DECLARE @runningValue BIGINT = 0

UPDATE #cteILIDetails SET GroupingFlag = @runningValue, @runningValue = CASE WHEN Price IS NULL OR Price <> 0 THEN @runningValue + 1 ELSE @runningValue END

Any help would be highly appreciated.


Sankalp Singhal

Very Important crosS Applying yaK Herder

52326 Posts

Posted - 04/30/2013 :  05:34:54  Show Profile  Reply with Quote
do you've any other column to determine the order? otherwise how will you determine what value to be set for the 0 rows? ideally there should be a unique id field or datetime field

SQL Server MVP
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.02 seconds. Powered By: Snitz Forums 2000