CODEWITHSUNDEEP
sqlms-access-2007
nfinium
nfinium

Reputation: 141

SQL get Max Column value for each Row

Sample Table : MyTable1

Col1    |    Col2     |     Col3
   3           10            5
  10           9             40
   1           2             6

The Output must be:

NewColumn
    10
    40
     6

As you can see, I just need to get the highest value from each row..

Is it possible by just using a sql select query?

Thanks in advance

Upvotes: 2

Views: 3110

Answers (3)

Maludasek
Maludasek

Reputation: 117

Example which works:

Table1:

Col - Text
Col1 - Number
Col2 - Number
Col3 - Number
Col4 - Number

Data:

col; col1; col2; col3; col4;
a;      1;    4;    6;    7;
b;      3;   66;   23;  235;
c;     34;  634;   11;   23;

Query:

SELECT Col, Max(colx) AS MaxOfColx
FROM 
(
SELECT Col, Col1 AS Colx From Table1 UNION ALL
SELECT Col, Col2 AS Colx From Table1 UNION ALL
SELECT Col, Col3 AS Colx From Table1 UNION ALL
SELECT Col, Col4 AS Colx From Table1
)
group by Col

Result:

Col; MaxOfColx
 a;          7
 b;        235
 c;        634

This will work for as many columns as you need.

Upvotes: 0

Andriy M
Andriy M

Reputation: 77657

I wonder if this would work in MS Access (can't verify myself, I'm afraid):

SELECT
  (
    SELECT MAX(Col)
    FROM (
      SELECT Col1 AS Col UNION ALL
      SELECT Col2        UNION ALL
      SELECT Col3
    ) s
  ) AS NewColumn
FROM yourtable

This works in the other family of database products by the same vendor, so I thought they might just happen to have added support for this in MS Access too.

Upvotes: 0

Taryn
Taryn

Reputation: 247630

You will need to use the MS Access function IIF() 

IIF(condition, valueiftrue, valueiffalse)

condition is the value that you want to test.

valueiftrue is the value that is returned if condition evaluates to TRUE.

valueiffalse is the value that is returned if condition evaluates to FALSE.

so your query would be

SELECT IIf(C12 > Col3, C12, Col3) as newColumn
FROM
(
    select IIF(Col1 > Col2,  Col1,  Col2) as C12, Col3
    from mytable1
) x

Upvotes: 2

Related Questions