How to Find Maximum of Multiple Columns? MS SQL

Today I want to share an interesting example that describes  how to find the maximum of multiple columns . Here is a filled table that contains the weekly sales report:
weekly sales report by day of week sql

It is necessary to find the  maximum number of sales for each product across table columns . In our case, each column is a day of the week. I highlighted in red the values ​​that should be obtained in our sample. To solve this problem, we will use the Table Value Constructor construction, which will  allow us to form a temporary table from our columns .

The result should be:

  1. Laptop 15
  2. Mouse 24
  3. Monitor 11

 

Here is a query:

SELECT
     [Id]
    ,[Product]
    ,(
        SELECT 
            MAX(SalesCount) 
        FROM (VALUES([Monday]), 
                    ([Tuesday]), 
                    ([Wednesday]), 
                    ([Thursday]), 
                    ([Friday]), 
                    ([Saturday]), 
                    ([Sunday])) AS CurrentWeekTbl1(SalesCount)
     ) as MaxSales
FROM [YarkulTestDb1].[dbo].[CurrentWeekSales]

And Result:
max of multiple columns

How does Table Value Constructor Works?

In order to better understand how ‘Table Value Constructor’ works, I will demonstrate it in a lighter example. Let’s assume we need to create a temporary selection of Ukrainian cities:

SELECT * FROM (VALUES ('Kiev'),('Kharkiv'),('Lugansk'),('Lviv'),('Donetsk'), ('Simferopol'), ('Mariupol')) as MyTable(MyColumnA)

table value constructor example

From Microsoft documentation: “ Specifies a set of row value expressions to be constructed into a table.  The Transact-SQL table value constructor allows multiple rows of data to be specified in a single DML statement. The table value constructor can be specified either as the VALUES clause of an INSERT … VALUES statement, or as a derived table in either the USING clause of the MERGE statement or the FROM clause.“

Leave a Comment