July 25, 2012 at 7:18 am
Hi
Suppose a table being
Create table myTable (ID int, col1 int, col2 int)
I know how to make a computed column being the sum of other column for the same ID e.g. "computed_column = col1 + col2". Getting the average would be "computed_column = (col1 + col2)/2"
But how to get the Max, Min?
Even "Sum(col1,col2)" or AVG(col1, col2) does not work as the formula for a computed column...
July 25, 2012 at 7:22 am
run your select into a CTE then get the min max from there
eg.
;with cte
as
(
select id, (col1+col2)/2 as whatever
from table where x y z
)
select id, min (whatever), max (whatever)
from cte
group by id
or something like that
***The first step is always the hardest *******
July 25, 2012 at 7:31 am
No sure to understand
I want to create a computed column in my table
So the query should be something like
"ALTER TABLE myTable ADD computed_Max AS (max(col1,col2)"
but the command "max" does not work like that
My question is more "how do you get the maximum value, for every row, of the two column col1 et col2" (in fact, I need to get the max value from 16 columns)
July 25, 2012 at 7:39 am
From BOL: -
A computed column is computed from an expression that can use other columns in the same table. The expression can be a noncomputed column name, constant, function, and any combination of these connected by one or more operators. The expression cannot be a subquery.
The only way to do what you're after would be to use a view, e.g.
CREATE VIEW myView AS
SELECT ID, col1, col2, MAX(col1) AS maxCol1, MAX(col2) AS maxCol2
FROM myTable
GROUP BY ID;
July 25, 2012 at 7:40 am
There's no built in function that gives you the maximum values from a list of column inputs (it's called GREATEST in some languages), you'd have to do it manually with a case statement or build a scalar function to do so from a list of inputs...
e.g.:
myColumn AS CASE WHEN col1>col2 THEN col1 ELSE col2 END
July 25, 2012 at 7:42 am
I would agree on that if I was not looking for a computed column
My best way, would be to calculate the values while entering the data via the interface, but what would append if someone changes the data directly into the column? I need a computed column that calculate the max for every row...
July 25, 2012 at 7:44 am
Hi, Can you please try the below...
ALTER TABLE #test ADD computed_Max AS (CASE WHEN COL1 < COL2 THEN COL1 ELSE COL2 END)
please give less than symbol in place of "<"
Regards,
Karthik.
SQL Developer.
July 25, 2012 at 7:45 am
HowardW (7/25/2012)
There's no built in function that gives you the maximum values from a list of column inputs (it's called GREATEST in some languages),[/code]
If there is no built in function, I'll have to forget about it then...
thanks!
July 25, 2012 at 7:47 am
You could do this:
Create table myTable (ID int, col1 int, col2 int);
insert into myTable values (1, 10, 15);
insert into myTable values (1, 20, 25);
insert into myTable values (1, 30, 45);
insert into myTable values (1, 40, 35);
insert into myTable values (2, 12, 15);
insert into myTable values (2, 10, 10);
insert into myTable values (2, 11, 15);
select
ID,
col1,
col2,
summ = (col1 + col2),
average = (col1 + col2) / 2.0,
maximum = MAX(col1 + col2) over (partition by ID),
minimum = MIN(col1 + col2) over (partition by ID)
from
myTable
July 25, 2012 at 7:51 am
Hi,
Below is the entire script which will meet your requirement.
create table #test ( id int, col1 int, col2 int)
ALTER TABLE #test ADD computed_Max AS (CASE WHEN COL1> COL2 THEN COL1 ELSE COL2 END)
insert into #test values(1,6,8)
insert into #test values(2,8,10)
select * from #test
update #test
set col1 = 10
where id = 2
select * from #test
update #test
set col1 = 1
where id = 2
Regards,
Karthik.
SQL Developer.
July 25, 2012 at 7:55 am
tilew-948340 (7/25/2012)
HowardW (7/25/2012)
There's no built in function that gives you the maximum values from a list of column inputs (it's called GREATEST in some languages),[/code]
If there is no built in function, I'll have to forget about it then...
thanks!
I think Iexpressed myself in a wrong way:
I want to create a computed column so I need the formula to create the query to create the computed column and not to get the query to get the maximum
thanks for trying thought...
July 25, 2012 at 7:56 am
tilew-948340 (7/25/2012)
HowardW (7/25/2012)
There's no built in function that gives you the maximum values from a list of column inputs (it's called GREATEST in some languages),[/code]
If there is no built in function, I'll have to forget about it then...
thanks!
It would be fairly trivial to build a scalar function to do it, but you'd have to know how many input columns you'd need up front and the data type. E.g.:
CREATE FUNCTION dbo.GREATEST
(
@Input1 INT ,
@Input2 INT ,
@Input3 INT ,
@Input4 INT ,
@Input5 INT
)
RETURNS INT
AS
BEGIN
DECLARE @max-2 INT
SELECT @max-2 = MAX(Input)
FROM ( SELECT @Input1 AS Input
UNION ALL
SELECT @Input2 AS Input
UNION ALL
SELECT @Input3 AS Input
UNION ALL
SELECT @Input4 AS Input
UNION ALL
SELECT @Input5 AS Input
) AS T1
RETURN @max-2
END
GO
ALTER TABLE #test
ADD Max_Value AS dbo.GREATEST(col1,col2,col3,col4,col5)
July 25, 2012 at 7:59 am
Karthiart (7/25/2012)
Hi,Below is the entire script which will meet your requirement.
create table #test ( id int, col1 int, col2 int)ALTER TABLE #test ADD computed_Max AS (CASE WHEN COL1< COL2 THEN COL1 ELSE COL2 END)
Ya... the case solution... the only problem I have with that, is that the real table gets 16 columns to compare to...
Thanks for trying...:-)
July 25, 2012 at 8:13 am
HowardW (7/25/2012)
It would be fairly trivial to build a scalar function to do it, but you'd have to know how many input columns you'd need up front and the data type. E.g.:
CREATE FUNCTION dbo.GREATEST(
@Input1 INT ,
@Input2 INT ,
@Input3 INT ,
@Input4 INT ,
@Input5 INT
)
RETURNS INT
AS
BEGIN
DECLARE @max-2 INT
SELECT @max-2 = MAX(Input)
FROM ( SELECT @Input1 AS Input
UNION ALL
SELECT @Input2 AS Input
UNION ALL
SELECT @Input3 AS Input
UNION ALL
SELECT @Input4 AS Input
UNION ALL
SELECT @Input5 AS Input
) AS T1
RETURN @max-2
END
GO
ALTER TABLE #test
ADD Max_Value AS dbo.GREATEST(col1,col2,col3,col4,col5)
If I understand well, you propose that, not only I would create a function to replace the "max" function but also to replace the computed column? I am not sure this solution would update its value automatically every time that we change a value in a column... am I wrong?
July 25, 2012 at 8:28 am
tilew-948340 (7/25/2012)
If I understand well, you propose that, not only I would create a function to replace the "max" function but also to replace the computed column? I am not sure this solution would update its value automatically every time that we change a value in a column... am I wrong?
Hmm, not sure I understand what you're asking. Yes, it would update if you changed a column in that row as any other deterministic function would - try it and see.
All it's doing is transposing the inputs into rows (unpivoting) then using the MAX aggregate function to pick the highest value. It's not "replacing" the MAX function, that's an aggregate function for getting the maximum value for all rows of a single column. I thought you needed a function that gives you the maximum value for all columns in a single row.
I don't understand what you mean by replace the computed column either, I thought you didn't have a max computed column, hence your question? I put a sample of how you could add a computed column to an existing table using the function that I created above, I've no idea how that relates to your actual tables...
My advice would be to try it out in a test environment and play with the data to make sure it gives you what you're after and if it doesn't, give us some real examples and sample data so we can try to assist.
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply