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.
Author |
Topic |
nirnir
Starting Member
10 Posts |
Posted - 2013-01-10 : 09:15:01
|
I have a tableA with fields : Group, val, max_val,low_valI need fields max_val,low_val to be computed : max_val= max(val) , low_val=low(val) for the same group . Attached a table with sample values .1. please help me with the most efficient update command . 2. considering the table is updated once a month , would it be better to create a view and have those fields (low_val,high_val) only in the view or adding them to the real table and recompute them after each update ?group __ val__ Low_val__ Max_valabc ____2________ 2 ____ 5abc ____4________ 2 ____ 5abc ____5________ 2 ____ 5def ____1________ 1 ____ 2def ____2________ 1 ____ 2ggg ____5________ 5 ____ 5 |
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2013-01-10 : 09:28:29
|
select t1.*, t2.low_val, t2.max_valfrom tbl t1join (select [group], low_val = min(val), max_val = max(val) from tbl group by [group]) t2on t1.[group] = t2.[group]for an updateupdate tblset low_val = t2.low_val, max_val = t2.max_valfrom tbl t1join (select [group], low_val = min(val), max_val = max(val) from tbl group by [group]) t2on t1.[group] = t2.[group]No need for a view.I would have thought a monthly updae would be simplest but depends on the table size and frequency of updates==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
nirnir
Starting Member
10 Posts |
Posted - 2013-01-10 : 10:15:00
|
Thanks,If I want another boolean field IS_MAX_VAL which should betrue in rows where val=max_valcan I update it in the same command ?(Actually my final aim is to have this field(IS_MAX_VAL ) in the index since most of the times I need to query by it) |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2013-01-10 : 11:04:24
|
update tblset low_val = t2.low_val, max_val = t2.max_val, is_max_val = case when max_val = t2.max_val then 'Y' else 'N' endfrom tbl t1join (select [group], low_val = min(val), max_val = max(val) from tbl group by [group]) t2on t1.[group] = t2.[group]A candidate for a filtered index?==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
nirnir
Starting Member
10 Posts |
Posted - 2013-01-10 : 11:49:13
|
Thanks Again,Regarding filtered index,In most cases I want to see only records which is_max_val = true (plus other conditions , for example clientNum=x). I have a primary index on clientNum and few other indexes forsorting (clientName,dates...)- should I add another regular index for is_max_val ?- if I add a filtered index for is_max_val should the filter expression be is_max_val ? what should be in the included columns,index columns ?sorry for my newbie questions |
|
|
|
|
|
|
|