set a value , find max - SQL Server Forums

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

All Forums

General SQL Server Forums

New to SQL Server Programming

set a value , find max

Author

Topic

er.bhupinder53
Starting Member

13 Posts

Posted - 2011-07-13 : 16:09:40

Hi guys, I am writing a query for a condition
"if colA = Match1
then I need to set a value for colB say "ABC001"
now for next data, if ColA = Match1 again, then it should find the maximum value for colB i.e ABC001 and add1 to it (i.e ABC002) and populate in colB and so on..

But if for next data, colA = Match2 then it should set the value "XYZ100"
again if next data, colA = match2, find max(XYZ100) add 1 (XYZ101) and populate in col B .

NOTE: ColA has values either Match 1 or Match2
ColB should have either "ABC***" or "XYZ***" depending upon match1 and match2.

Many thanks in advance.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-13 : 17:56:05

I read your posting couple of times (well, more than couple of times), but didn't quite follow the logic or how a query should written. Can you post:

1. The query you have so far.
2. Table DDL's with some sample data. Brett's blog here may be of help in getting the table DDL.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-07-13 : 18:44:25

[code]
select colA, colB = case colA
when 'Match1' then 'ABC'
when 'Match2' then 'XYZ'
end
+ right('000' + convert(varchar(10), row_number() over(partition by colA order by colA)), 3)
from yourtable
[/code]

KH
[spoiler]Time is always against us[/spoiler]

er.bhupinder53
Starting Member

13 Posts

Posted - 2011-07-13 : 19:12:25

Well, I made myself as clear as possible.
here is the query I used :

DECLARE @colB As Varchar(25)
if colA = 'Match1'
SET @ColB = ABC001
UPDATE my_Table
SET @ColB= MAX(colB) = @colB+1
Else Set @ColB = XYZ100
UPDATE my_Table
SET @colB = MAX(colB) = @colB+1

Its giving me errors. Please Help

er.bhupinder53
Starting Member

13 Posts

Posted - 2011-07-13 : 19:36:53

@khtan : Thank you so much , It solved the most part but I need to update the my_Table with those values.

Also I want to fix the length lets say (3for ABC and 3 for integers)
so for 10th value, it should say "ABC010" instead of "ABC0010"

Also I want to start values from lets say 5

so first value should be "ABC005" and sixth value should be "ABC010".

Thanks Again.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-07-13 : 19:55:52

[code]
update t
~~select colA,~~
set colB = newcolB
from
(
select colB, newcolB = case colA
when 'Match1' then 'ABC'
when 'Match2' then 'XYZ'
end
+ right('000' + convert(varchar(10), row_number() over(partition by colA order by colA) + 4), 3)
from yourtable
)t
[/code]

KH
[spoiler]Time is always against us[/spoiler]

er.bhupinder53
Starting Member

13 Posts

Posted - 2011-07-13 : 20:03:59

Thats what I originally tried but it gives me the error:

Windowed functions can only appear in the SELECT or ORDER BY clauses.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-07-13 : 20:07:35

edited my last post.

KH
[spoiler]Time is always against us[/spoiler]

er.bhupinder53
Starting Member

13 Posts