0

I am looking for a way to pick the highest and lowest value (integer) from a single row in table. There are 4 columns that i need to compare together and get highest and lowest number there is.

The table looks something like this...

id | name | col_to_compare1 | col_to_compare2 | col_to_compare3 | col_to_compare4

1 | John | 5 | 5 | 2 | 1

2 | Peter | 3 | 2 | 4 | 1

3 | Josh | 3 | 5 | 1 | 3

Can you help me, please? Thanks!

backtrack
  • 7,996
  • 5
  • 52
  • 99
dusty
  • 33
  • 4
  • what is the desired output ? – backtrack Nov 29 '16 at 14:05
  • Another column with highest value and next one with the lowest one. – dusty Nov 29 '16 at 14:06
  • 2
    Possible duplicate of [SQL Server equivalent to Oracle LEAST?](http://stackoverflow.com/questions/1972051/sql-server-equivalent-to-oracle-least) – JohnHC Nov 29 '16 at 14:07
  • Possible duplicate of [SQL MAX of multiple columns?](http://stackoverflow.com/questions/71022/sql-max-of-multiple-columns) – NePh Nov 29 '16 at 14:09
  • Poor table design. Why all those col_to_compare columns? – jarlh Nov 29 '16 at 14:09
  • 2
    This is a sign of a broken data model - if you have values that you're wanting to compare or compute aggregates across, it's an indication that they're the same "type" of values and ought to be stored (across multiple rows) in a single column. Also, some of your data (1,2,3,4), if it's actually relevant, has been embedded into *metadata* where it's less easy to query. – Damien_The_Unbeliever Nov 29 '16 at 14:10
  • Yes, i know that it is a bad table design, however this is what I have to work with... – dusty Nov 29 '16 at 14:12

5 Answers5

3

You can do this using CROSS APPLY and the VALUES clause. Use VALUES to group all your compared columns and then select the max.

SELECT 
        MAX(d.data1) as MaxOfColumns
        ,MIN(d.data1) as MinOfColumns
        ,a.id
        ,a.name
  FROM YOURTABLE as a 
  CROSS APPLY   (    
                VALUES(a.col_to_compare1)
                     ,(a.col_to_compare2)
                     ,(a. col_to_compare3)
                     ,(a.col_to_compare4)
                     ,(a. col_to_compare5)
                ) as d(data1) --Name the Column
   GROUP BY a.id
            ,a.name
Arthur D
  • 592
  • 4
  • 10
1

Assuming you are looking for min/max per row

Declare @YourTable table (id int,name varchar(50),col_to_compare1 int,col_to_compare2 int,col_to_compare3 int,col_to_compare4 int)
Insert Into @YourTable values
(1,'John',5,5,2,1),
(2,'Peter',3,2,4,1),
(3,'Josh',3,5,1,3)

Select A.ID
      ,A.Name
      ,MinVal = min(B.N)
      ,MaxVal = max(B.N)
 From  @YourTable A
 Cross Apply (Select N From (values(a.col_to_compare1),(a.col_to_compare2),(a.col_to_compare3),(a.col_to_compare4)) N(N) ) B
 Group By A.ID,A.Name

Returns

ID  Name    MinVal  MaxVal
1   John    1       5
3   Josh    1       5
2   Peter   1       4
John Cappelletti
  • 79,615
  • 7
  • 44
  • 66
0

A way to do this is to "break" apart the data

declare @table table (id int, name varchar(10), col1 int, col2 int, col3 int, col4 int)

insert into @table values (1 , 'John' , 5 , 5 , 2 , 1)
insert into @table values (2 , 'Peter' , 3 , 2 , 4 , 1)
insert into @table values (3 , 'Josh' , 3 , 5 , 1 , 3)


;with stretch as
(
    select id, col1 as col from @table
    union all
    select id, col2 as col from @table
    union all
    select id, col3 as col from @table
    union all
    select id, col4 as col from @table
)

select
    t.id, 
    t.name, 
    agg.MinCol, 
    agg.MaxCol
from @table t
    inner join 
    (
        select
            id, min(col) as MinCol, max(col) as MaxCol
        from stretch
        group by id
    ) agg
        on t.id = agg.id
DForck42
  • 19,789
  • 13
  • 59
  • 84
0

These solutions keep the current rows and add additional columns of min/max.

select      *

from        t cross apply   

           (select  min(col)   as min_col
                   ,max(col)   as max_col

            from    (
                        values

                        (t.col_to_compare1)
                       ,(t.col_to_compare2)
                       ,(t.col_to_compare3)    
                       ,(t.col_to_compare4)

                    ) c(col)
            ) c

OR

select      *
           ,cast ('' as xml).value ('min ((sql:column("t.col_to_compare1"),sql:column("t.col_to_compare2"),sql:column("t.col_to_compare3"),sql:column("t.col_to_compare4")))','int')    as min_col
           ,cast ('' as xml).value ('max ((sql:column("t.col_to_compare1"),sql:column("t.col_to_compare2"),sql:column("t.col_to_compare3"),sql:column("t.col_to_compare4")))','int')    as max_col

from        t   

+----+-------+-----------------+-----------------+-----------------+-----------------+---------+---------+
| id | name  | col_to_compare1 | col_to_compare2 | col_to_compare3 | col_to_compare4 | min_col | max_col |
+----+-------+-----------------+-----------------+-----------------+-----------------+---------+---------+
| 1  | John  | 5               | 5               | 2               | 1               | 1       | 5       |
+----+-------+-----------------+-----------------+-----------------+-----------------+---------+---------+
| 2  | Peter | 3               | 2               | 4               | 1               | 1       | 4       |
+----+-------+-----------------+-----------------+-----------------+-----------------+---------+---------+
| 3  | Josh  | 3               | 5               | 1               | 3               | 1       | 5       |
+----+-------+-----------------+-----------------+-----------------+-----------------+---------+---------+
David דודו Markovitz
  • 42,900
  • 6
  • 64
  • 88
0

Seems simple enough

SELECT min(col1), max(col1), min(col2), max(col2), min(col3), max(col3), min(col4), max(col4) FROM table

Gives you the Min and Max for each column.

Following OP's comment, I believe he may be looking for a min/max grouped by the person being queried against.

So that would be:

SELECT name, min(col1), max(col1), min(col2), max(col2), min(col3), max(col3), min(col4), max(col4) FROM table GROUP BY name
TolMera
  • 452
  • 10
  • 25