June 2, 2011 at 3:01 am
Hey Guys,
How can we find maximum value on column level? Suppose we have table A with four columns col1,col2,col3,col4, Now my query look likes this:
Select col1, col2,col3,col4,
(col1 + col2) as addcol1,
(col2 + col3) as addcol2,
(col3 + col4) as addcol3,
Max(addcol1,addcol2,addcol3) as maxvalue
from Table A
I am getting error as max accepts one argument, I cannot use case statement as table is already bulky and it will make my query more expensive so please let me know an appropriate solution?
June 2, 2011 at 3:19 am
Why don't you add a computed (persisted) column with a CASE statement expression ( 😉 ) returning the max value you're looking for?
You could also use UNPIVOT against the addcol columns and then query the max value.
June 2, 2011 at 7:16 am
abhisheksrivastava85 (6/2/2011)
... I cannot use case statement as table is already bulky and it will make my query more expensive ...
As JC would say, SQL has no CASE statement, it's a CASE expression. 😀 It will not make the query more expensive because it calculates the result in one pass. It is not going to be pretty with a lot of columns, but it will be quick.
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
June 2, 2011 at 7:32 am
Following from Lutz's suggestion, here is an example of a computed, persisted column:
create table Max_test(col1 int, col2 int, col3 int, col4 int,
max1 as
case when (col1 + col2) > (col2 + col3) then
case when (col1 + col2) > (col3 + col4) then (col1 + col2) else (col3 + col4) end
else
case when (col2 + col3) > (col3 + col4) then (col2 + col3) else (col3 + col4) end
end PERSISTED
);
insert into Max_test values (1,2,3,4), (4,1,2,3);
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
June 2, 2011 at 8:00 am
Thanks guys for your help, somehow i managed to find the optimized solution for finding maximum value on column level. The code which is used is as follows:
select max(MAXValue)
from (select (col1) as MAXValue
union all
select (col2)
union all
select (col3)
union all
select (col4)
UNION ALL
SELECT (col5)) as maxval)
June 2, 2011 at 8:09 am
Well done abhisheksrivastava85... I must say i did not see that one. :doze:
June 2, 2011 at 8:23 am
abhisheksrivastava85 (6/2/2011)
Thanks guys for your help, somehow i managed to find the optimized solution for finding maximum value on column level. The code which is used is as follows:select max(MAXValue)
from (select (col1) as MAXValue
union all
select (col2)
union all
select (col3)
union all
select (col4)
UNION ALL
SELECT (col5)) as maxval)
Mmmm.... please will you post the exact code you're using? You are SELECTing columns but there is no FROM clause, so I can't see how it'll work. I'm just worried that you'll get the wrong results because the MAX value from one column may not be in the same row as the MAX value from another.
John
June 2, 2011 at 9:56 am
Just for fun
with cte (RowID,A,B,C,D,E) as
(select 1,1,2,3,4,5 union all
select 2,4,1,5,3,2 union all
select 3,7,4,1,2,2
)
,cte2 as
(select RowID,CA.* from cte
cross apply (values (cte.A),(cte.B),(cte.C),(cte.D),(cte.E)) CA (val)
)
select RowID, MAX(val) as MaxValue
from cte2
group by RowID
order by RowID
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
June 2, 2011 at 10:17 am
Fastest performance. (One pass... No Sort Required)
with cte (RowID,A,B,C,D,E) as
(
select 1,1,2,3,4,5 union all
select 2,3,1,5,3,2 union all
select 3,7,4,1,2,5
)
,cte2 as
(select *, case when A > B then A else B end as MaxAB
, case when C > D then C else D end as MaxCD
from cte
)
,cte3 as
(select *, case when MaxCD > MaxAB then MaxCD else MaxAB end as MaxAD
, case when MaxCD > E then MaxCD else E end as MaxCE
from cte2
)
select Rowid,case when MaxCE > MaxAD then MaxCE else MaxAD end as MaxVal
from cte3
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
June 21, 2011 at 12:25 pm
this looks good only for SQL 2008
To Bad it doesn't work with SQL 2005
June 21, 2011 at 12:57 pm
Scott, will you clarify what you think won't work on 2005? The last thing I posted works just fine on a 2005 instance.
By the way, there are two o's in "Too bad".
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
June 21, 2011 at 2:29 pm
The Dixie Flatline (6/2/2011)
Fastest performance. (One pass... No Sort Required)
with cte (RowID,A,B,C,D,E) as
(
select 1,1,2,3,4,5 union all
select 2,3,1,5,3,2 union all
select 3,7,4,1,2,5
)
,cte2 as
(select *, case when A > B then A else B end as MaxAB
, case when C > D then C else D end as MaxCD
from cte
)
,cte3 as
(select *, case when MaxCD > MaxAB then MaxCD else MaxAB end as MaxAD
, case when MaxCD > E then MaxCD else E end as MaxCE
from cte2
)
select Rowid,case when MaxCE > MaxAD then MaxCE else MaxAD end as MaxVal
from cte3
I think that code will have problems if any of the columns are nullable, which they almost alway are when you have a non-normalized structure like this. Once you add checks for null columns, it gets ugly fast.
The code on the following link demos both methods that have been posted on this thread, along with test code to demo the relative performance.
Method 1 uses a UNION ALL sub query for all the columns with a MAX. It is much simpler to code and test, especially when you get much past 4 columns. Adding another column is as simple as adding one more SELECT to the subquery.
Method 2 uses a CASE statement to determine the MAX. It is much more complex to code (and test), and gets exponentially harder to code as the number of columns goes up.
MIN/MAX Across Multiple Columns
June 21, 2011 at 2:43 pm
Good point about the null columns. I hadn't thought about that.
I believe this fixes it though. Not too ugly and still gets the job done in one pass.
;with cte (RowID,A,B,C,D,E) as
(
select 1,null,2,3,4,5 union all
select 2,3,null,1,3,2 union all
select 3,7,4,null,2,5 union all
select 4,5,2,3,null,6 union all
select 5,2,null,null,null,null
)
,cte1 as (select RowID, ISNULL(A,0) as A, ISNULL(B,0) as B, ISNULL(C,0) as C, ISNULL(D,0) as D, ISNULL(E,0) as E from cte)
,cte2 as
(select *, case when A > B then A else B end as MaxAB
, case when C > D then C else D end as MaxCD
from cte1
)
,cte3 as
(select *, case when MaxCD > MaxAB then MaxCD else MaxAB end as MaxAD
, case when MaxCD > E then MaxCD else E end as MaxCE
from cte2
)
select Rowid,case when MaxCE > MaxAD then MaxCE else MaxAD end as MaxVal
from cte3
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
June 21, 2011 at 2:53 pm
The Dixie Flatline (6/21/2011)
Good point about the null columns. I hadn't thought about that.I believe this fixes it though. Not too ugly and still gets the job done in one pass.
;with cte (RowID,A,B,C,D,E) as
(
select 1,null,2,3,4,5 union all
select 2,3,null,1,3,2 union all
select 3,7,4,null,2,5 union all
select 4,5,2,3,null,6 union all
select 5,2,null,null,null,null
)
,cte1 as (select RowID, ISNULL(A,0) as A, ISNULL(B,0) as B, ISNULL(C,0) as C, ISNULL(D,0) as D, ISNULL(E,0) as E from cte)
,cte2 as
(select *, case when A > B then A else B end as MaxAB
, case when C > D then C else D end as MaxCD
from cte1
)
,cte3 as
(select *, case when MaxCD > MaxAB then MaxCD else MaxAB end as MaxAD
, case when MaxCD > E then MaxCD else E end as MaxCE
from cte2
)
select Rowid,case when MaxCE > MaxAD then MaxCE else MaxAD end as MaxVal
from cte3
I don't think that works as a general solution.
You don't know that there are no negative values in the table, so the zero that you are replacing the NULL with might be returned as the max value. Also, if all the values are null, you would return a zero when it should return a null.
You could use a value like -2,147,483,648 if it is an integer since that is the smallest possible int, but you really don't know for sure that value does not occur in the data, and it would still give you a bad result when all columns are null.
June 21, 2011 at 4:55 pm
Michael, if I may, you are being nit-picky at this point. 😛 Let's not assume the columns hold INT values. If we want to handle BIGINT columns, we really should replace the zeroes in the isnull function with -9,223,372,036,854,775,808.
Seriously, the OP had already said he had a solution, I just threw a couple of other approaches out there for kicks. Of course, I've taken someone else to task for doing the same thing, so I will take my beating like a man.
Thank you for the link though. I will be sure to read it.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply