Sql-server – Get maximum, minimum values across columns

sql serversql-server-2000

I want to get the minimum and maximum across multiple columns in a row. I found various working queries using CROSS APPLY, UPIVOT and VALUES, e.g. like this one:

select column1, column2, code1,
       (qty2), (qty3), (qty4), (qty5),
       MinVal, MaxVal 
from dummydata 
    cross apply (select MIN(NULLIF (d ,0)) MinVal, 
                        Max(d) MaxVal 
                 FROM (VALUES (qty2), 
                              (qty3), 
                              (qty4), 
                              (qty5)
                      ) AS a(d) 
                )  A

These all are working in SQL Server 2005 or above, but I need a solution for SQL Server 2000 (database compatibility level 80). How can I do that?

DDL:

CREATE TABLE [dbo].[dummydata](
[column1] [nvarchar](255) NULL,
[column2] [nvarchar](255) NULL,
[code1] [nvarchar](255) NULL,
[qty2] [float] NULL,
[qty3] [float] NULL,
.......
[qty20] [float] NULL)

Query output:

enter image description here

Best Answer

It's not going to be pretty (and I have no 2000 installation to test) but this should work.

The pk is the PRIMARY KEY of the table (or a column with UNIQUE NOT NULL constraints):

SELECT d.column1, d.column2, d.code1,
       d.qty2, d.qty3, d.qty4, d.qty5, ..., d.qty20,
       m.MinVal, m.MaxVal 
FROM 
    dummydata AS d 
  JOIN
    ( SELECT q.pk,
             MIN(NULLIF(q.qty, 0)) AS MinVal, 
             MAX(q.qty) AS MaxVal 
      FROM 
        ( SELECT pk, qty2 AS qty FROM dummydata UNION ALL
          SELECT pk, qty3 FROM dummydata UNION ALL
          SELECT pk, qty4 FROM dummydata UNION ALL
          SELECT pk, qty5 FROM dummydata UNION ALL
          ...
          SELECT pk, qty20
        ) AS q
      GROUP BY pk
    ) AS m
    ON m.pk = d.pk ;

Alternative is to use correlated subqueries in the SELECT list:

SELECT d.column1, d.column2, d.code1,
       d.qty2, d.qty3, d.qty4, d.qty5, ..., d.qty20,
       ( SELECT MIN(NULLIF(q.qty, 0)) 
         FROM 
           ( SELECT d.qty2 AS qty UNION ALL
             SELECT d.qty3 UNION ALL
             ...
             SELECT d.qty20
           ) AS q
       ) AS MinVal, 
       ( SELECT MAX(q.qty) 
         FROM 
           ( SELECT d.qty2 AS qty UNION ALL
             SELECT d.qty3 UNION ALL
             ...
             SELECT d.qty20
           ) AS q
       ) AS MaxVal 
FROM 
    dummydata AS d ;

Tested in rextester.com

Related Question