Menu
I have a table with columns ID, A, B, C, D, E... (10 numeric columns in all)
For each row, I need to find which column has the largest value, and what that value is.
E.G. Here's 1 row of my table:ID A B C D E F G H I JXY 5 4 9 5 0 1 3 2 1 7
I want to generate 2 new columns:
maxvalue, which would equal 9, and
maxcol, which would equal "C"
Any advice, beyond a massive IF statement?
616
To find the maximum value of a column, use the MAX() aggregate function; it takes a column name or an expression to find the maximum value. In our example, the subquery returns the highest number in the column grade (subquery: SELECT MAX(grade) FROM student ).
To find the max value of a column, use the MAX() aggregate function; it takes as its argument the name of the column for which you want to find the maximum value. If you have not specified any other columns in the SELECT clause, the maximum will be calculated for all records in the table.
AS we saw here today, there are several ways to obtain the maximum value across multiple columns. These include using the GREATEST() function, or by getting a bit creative with the MAX() function.
In SQL Server there are several ways to get the MIN or MAX of multiple columns including methods using UNPIVOT, UNION, CASE, etc… However, the simplest method is by using FROM … VALUES i.e. table value constructor. Let's see an example. In this example, there is a table for items with five columns for prices.
I don't have a sql processor to hand, but something along the lines of
select id , colName
from
(select id, 'A' as colName, a as value union all
select id, 'B' as colName, b as value union all
select id, 'C' as colName, c as value union all
select id, 'D' as colName, d as value union all
select id, 'E' as colName, e as value union all
select id, 'F' as colName, f as value union all
select id, 'G' as colName, g as value union all
select id, 'H' as colName, h as value union all
select id, 'I' as colName, i as value union all
select id, 'J' as colName, j as value)
group by id having max(value)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
