SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
Register Now and get your question answered!
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Update in SQL Server
 New Topic  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  
 New 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.03 seconds. Powered By: Snitz Forums 2000