Logic required for retaining previous value

Question

Post reply

Logic required for retaining previous value

Oracle_91

SSChampion

Points: 14548
More actions
November 24, 2009 at 11:10 pm

#214250

Hi,
Have a small requirement. I have a table with two columns and i require the below expected output.
create table test1
(c1 int,
c2 int
)
insert into test1
select 1,2
union all
select 2,3
union all
select 6,8
union all
select 7,7
union all
select 8,8
union all
select 4,9
union all
select 9,9
select c1,c2 from test1
For the first row the value for c3 should start from 1.
For the next record, we need compare c1 and c2 columns.
The logic is as follows
Case 1
-------
If the c1 == c2 then we need to retain the previous c3 value.
Case 2
--------
if c1 != c3 then we need to increment the previous value + 1 and
display it as C3 column.
Case 3
-------
a.In the first record itself C1 == C2 i.e equal then keep 0 for C3 column in the ouput.
b. if the first record itself C1 ! = 2 then keep 1 as for C3 column in the ouput.
For every record we need, keep the previous record intact.
Expected output
===============
C1 C2 C3
121
232
683
773 -- bcz C1 and C2 is equal
883 -- bcz C1 and C2 is equal
494
994
725
Any help would be greatly appreciated.
Thanks in Advance.

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

Nabha SSCrazy Eights Points: 8534 More actions · Answer 1

based on which column are you saying that 1, 2 is your first record? ( i mean in which order?)

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

Oracle_91 SSChampion Points: 14548 More actions · Answer 2

Sorry! my bad.

Small correction in Case 3: b)

b. if the first record itself C1 ! = C2 then keep 1 as for C3 column in the ouput.

All the comparisons should be done basing on C1 and C2 columns only.

Nabha SSCrazy Eights Points: 8534 More actions · Answer 3

yeah thats fine, but how do you identify your 'first' record?

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

arun.sas SSChampion Points: 11831 More actions · Answer 4

create table #test1

(

slno int identity(1,1),

c1 int,

c2 int

)

insert into #test1 (c1,c2)

select 1,2

union all

select 2,3

union all

select 6,8

union all

select 7,7

union all

select 8,8

union all

select 4,9

union all

select 9,9

select a.slno,a.c1,a.c2,

(

select count(case when b.slno = 1 and b.c1-b.c2 = 0 then null

when b.slno = 1 and b.c1 <> b.c2 then 1

when b.slno <> 1 and b.c1-b.c2 <> 0 then 1

else null end)

from #test1 b

where b.slno <= a.slno

)as c3

from #test1 a

Nabha SSCrazy Eights Points: 8534 More actions · Answer 5

Ok, with lot of assumptions. Here you go. Is this what you need?

;with CTE (c1,c2, row_num) as

(select c1,c2, row_number() OVER (order by C1) as row_num from

#test1)

Select C1,C2,

CASE WHEN c1= c2 AND Row_num = 1 THEN 0

WHEN c1 = C2 THEN (select Top 1 row_num

from CTE t1

where t1.c1 <> t1.c2

and t1.Row_num < t.row_num

order by t1.row_num desc)

ELSE row_num END as C3

from

CTE t

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

Oracle_91 SSChampion Points: 14548 More actions · Answer 6

Oracle_91

SSChampion

Points: 14548

November 25, 2009 at 2:04 am

#1083866

Sweet! 🙂

Thank You.

Oracle_91 SSChampion Points: 14548 More actions · Answer 7

Hi, small change. I would like to have one more column say “flag”. So whenever there is a change in the flag value, the C3 column value has to start from 1 again.

alter table #test1

add flag int

update #test1

set flag =1

insert into #test1(c1,c2,flag)

select 1,2,2

union all

select 2,4,2

union all

select 3,3,2

union all

select 4,2,2

union all

select 5,5,2

Oracle_91 SSChampion Points: 14548 More actions · Answer 8

I managed to fix this.

select a.slno,a.c1,a.c2,a.flag,

(select count(case when b.slno = 1 and b.c1-b.c2 = 0 then null -- for the first record if c1-c2 = 0

when b.slno = 1 and b.c1 <> b.c2 then 1 -- for the first record if c1 != c2

when b.slno <> 1 and b.c1-b.c2 <> 0 then 1 -- for all the remaining recs when serial no != 1

else null end) as ComputedColumn

from #test1 b

where b.slno <= a.slno and b.flag=a.flag

)as c3

from #test1 a

Thanks!

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 9

mahesh.vsp (11/25/2009)
I managed to fix this.
select a.slno,a.c1,a.c2,a.flag,
(select count(case when b.slno = 1 and b.c1-b.c2 = 0 then null -- for the first record if c1-c2 = 0
when b.slno = 1 and b.c1 <> b.c2 then 1 -- for the first record if c1 != c2
when b.slno <> 1 and b.c1-b.c2 <> 0 then 1 -- for all the remaining recs when serial no != 1
else null end) as ComputedColumn
from #test1 b
where b.slno <= a.slno and b.flag=a.flag
)as c3
from #test1 a
Thanks!

Oh my goodness, no! While all the solutions seem fast because of the low rowcounts, all of the solutions posted so far have CPU crushing Triangular Joins in them that can be literally millions of times worse than a cursor! See the following article and read why Triangular Joins are so bad whilst I workup up a nice little solution that will do a million rows in under seven seconds.

http://www.sqlservercentral.com/articles/T-SQL/61539/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 10

Apologies for the delay... I was drawn aside by other duties. Here's the code... details are in the comments. You'll be totally amazed at how fast this "Quirky Update" actually is.

--=====================================================================================================================

-- Create the test table. This is not part of the solution. It's just to create a demo table.

--=====================================================================================================================

--===== Conditionally drop the test table to make reruns easy

IF OBJECT_ID('TempDB..#Test1','U') IS NOT NULL

DROP TABLE #Test1

--===== Create the test table with the necessary clustered index (a clustered Primary Key in this case) to maintain

-- the necessary order for the upcoming "Quirky Update".

CREATE TABLE #Test1

(

RowNum INT IDENTITY(1,1) NOT NULL,

Col1 INT,

Col2 INT,

Flag INT,

Col3 INT,

CONSTRAINT PK_#Test1_RowNum

PRIMARY KEY CLUSTERED (RowNum)

)

--===== Populate the table with the given test data

INSERT INTO #Test1

(Col1, Col2, Flag)

SELECT 1,2,1 UNION ALL

SELECT 2,3,1 UNION ALL

SELECT 6,8,1 UNION ALL

SELECT 7,7,1 UNION ALL

SELECT 8,8,1 UNION ALL

SELECT 4,9,1 UNION ALL

SELECT 9,9,1 UNION ALL

SELECT 1,2,2 UNION ALL

SELECT 2,4,2 UNION ALL

SELECT 3,3,2 UNION ALL

SELECT 4,2,2 UNION ALL

SELECT 5,5,2

--=====================================================================================================================

-- Now, solve the problem in a very high performance manner using a single UPDATE that follows the clustered

-- index order and the natural "Pseudo-Cursor" behind the scenes of the "Quirky Update".

--=====================================================================================================================

--===== Declare some obviously named variables to remember information from the "previous row"

DECLARE @PrevFlag INT,

@PrevCount INT

--===== Preset the variables to a known starting postition other than NULL

SELECT @PrevFlag = 0,

@PrevCount = 1

--===== Do the update in clustered index order using the variables to remember the content of the "previous row" as

-- you would in any programming language for a problem of this nature.

UPDATE #Test1

SET @PrevCount = Col3 = CASE

WHEN Col1 = Col2

AND Flag = @PrevFlag

THEN @PrevCount

WHEN Flag <> @PrevFlag

THEN 1

ELSE @PrevCount + 1

END,

@PrevFlag = Flag

--===== Show the results

SELECT *

FROM #Test1

ORDER BY RowNum

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Nabha SSCrazy Eights Points: 8534 More actions · Answer 11

oops.. what an 'RBAR' my query was!

This one is nice, thanks Jeff.

OP wanted a case

Case 3
-------
a.In the first record itself C1 == C2 i.e equal then keep 0 for C3 column
in the ouput.
b. if the first record itself C1 ! = 2 then keep 1 as for C3 column in the
ouput.

Hoping that you dont mind, I tried to change it to handle the above

case as well.

UPDATE #Test1

SET @PrevCount = Col3 = CASE

WHEN Col1 = Col2 AND Flag = @PrevFlag THEN

@PrevCount

WHEN Col1 = Col2 AND Flag <>@PrevFlag

THEN 0

WHEN Col1 <> Col2 AND Flag <>

@PrevFlag THEN 1

ELSE @PrevCount + 1

END,

@PrevFlag = Flag

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

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 12

Heh.... Nicely done but, considering the following error and the fact that the code I posted does actually give the correct output, I'm thinking that you just read the code instead of running it and didn't see the logic involved and assumed that my code didn't work... there's no need for the 3rd WHEN statement...

Msg 207, Level 16, State 1, Line 73

Invalid column name 'PrevFlag'.

I don't see where in any of the write-ups the op posted about keeping a 0.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 13

Heh... now I see it... the OP snuck it into code. Nothing like a little hidden "added scope", huh? 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 14

Wow... egg on my face and not enough coffee in the gullet... my apologies...

a.In the first record itself C1 == C2 i.e equal then keep 0 for C3 column in the ouput.
b. if the first record itself C1 ! = 2 then keep 1 as for C3 column in the ouput.

... if it were a snake, it would have bit me. Not sure what I was looking at but I totally missed that. Sorry Nabha.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)