| Author |
 Topic  |
|
|
nirnir
Starting Member
5 Posts |
 Posted - 01/10/2013 : 09:15:01
|
I have a tableA with fields : Group, val, max_val,low_val
I 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_val
abc ____2________ 2 ____ 5
abc ____4________ 2 ____ 5
abc ____5________ 2 ____ 5
def ____1________ 1 ____ 2
def ____2________ 1 ____ 2
ggg ____5________ 5 ____ 5
|
Edited by - nirnir on 01/10/2013 09:23:03
|
|
|
nigelrivett
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 01/10/2013 : 09:28:29
|
select t1.*, t2.low_val, t2.max_val
from tbl t1
join (select [group], low_val = min(val), max_val = max(val) from tbl group by [group]) t2
on t1.[group] = t2.[group]
for an update
update tbl
set low_val = t2.low_val, max_val = t2.max_val
from tbl t1
join (select [group], low_val = min(val), max_val = max(val) from tbl group by [group]) t2
on 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
5 Posts |
Posted - 01/10/2013 : 10:15:00
|
Thanks,
If I want another boolean field IS_MAX_VAL which should be
true in rows where val=max_val
can 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
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 01/10/2013 : 11:04:24
|
update tbl
set low_val = t2.low_val, max_val = t2.max_val, is_max_val = case when max_val = t2.max_val then 'Y' else 'N' end
from tbl t1
join (select [group], low_val = min(val), max_val = max(val) from tbl group by [group]) t2
on 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
5 Posts |
Posted - 01/10/2013 : 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 for
sorting (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 |
|
|
| |
Topic |
|
update with other rows function
