CODEWITHSUNDEEP
sql-server
Waller
Waller

Reputation: 1873

Finding which COLUMN has a max( value per row

MSSQL

Table looks like so

ID    1   | 2  |  3  |  4  |  5 
AA1   1   | 1  |  1  |  2  | 1

any clues on how I could make a query to return

ID  |  MaxNo
AA1 | 4

, usign the above table example? I know I could write a case blah when statement, but I have a feeling there's a much simpler way of doing this

Upvotes: 0

Views: 953

Answers (2)

Mikael Eriksson
Mikael Eriksson

Reputation: 139010

You can use a cross apply where you do max() over the columns for one row.

select T1.ID,
       T2.Value
from YourTable as T1
  cross apply
    (
    select max(T.Value) as Value
    from (values (T1.[1]),
                 (T1.[2]),
                 (T1.[3]),
                 (T1.[4]),
                 (T1.[5])) as T(Value)
    ) as T2

If you are on SQL Server 2005 you can use union all in the derived table instead of values().

select T1.ID,
       T2.Value
from YourTable as T1
  cross apply
    (
    select max(T.Value) as Value
    from (select T1.[1] union all
          select T1.[2] union all
          select T1.[3] union all
          select T1.[4] union all
          select T1.[5]) as T(Value)
    ) as T2

SQL Fiddle

Upvotes: 2

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239824

You can use UNPIVOT to get these comparable items, correctly1, into the same column, and then use ROW_NUMBER() to find the highest valued row2:

declare @t table (ID char(3) not null,[1] int not null,[2] int not null,
                 [3] int not null,[4] int not null,[5] int not null)
insert into @t (ID,[1],[2],[3],[4],[5]) values
('AA1',1,1,1,2,1)

;With Unpivoted as (
select *,ROW_NUMBER() OVER (ORDER BY Value desc) rn
from @t t UNPIVOT (Value FOR Col in ([1],[2],[3],[4],[5])) u
)
select * from Unpivoted where rn = 1

Result:

ID   Value       Col                       rn
---- ----------- ------------------------- --------------------
AA1  2           4                         1

1 If you have data from the same "domain" appearing in multiple columns in the same table (such that it even makes sense to compare such values), it's usually a sign of attribute splitting, where part of your data has, incorrectly, been used to form part of a column name.

2 In your question, you say "per row", and yet you've only given a one row sample. If we assume that ID values are unique for each row, and you want to find the maximum separately for each ID, you'd write the ROW_NUMBER() as ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Value desc) rn, to get (I hope) the result you're looking for.

Upvotes: 3

Related Questions