How to Find Maximum of Multiple Columns? MS SQL

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 , ,( SELECT MAX(SalesCount) FROM (VALUES(), (), (), (), (), (), ()) AS CurrentWeekTbl1(SalesCount) ) as MaxSales
FROM ..

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."