Using a SQL WHERE clause on Max value of Multiple Columns

Question

I found a great solution to return the max value of multiple columns (SQL MAX of multiple columns?). See below. It works great for my application, but I have no idea how to run a WHERE on the [MaxDate] value. I can't seem to figure our the column name.

SELECT [Other Fields],
    (SELECT Max(v) 
     FROM (VALUES (date1), (date2), (date3),...) AS value(v)) as [MaxDate]
FROM [YourTableName]
WHERE [MaxDate] = '2016-01-01'  <---I know this is wrong but need help

Which [DBMS](https://en.wikipedia.org/wiki/DBMS) product are you using? Postgres? Oracle? "_SQL_" is just a query language, not the name of a specific database product. — , Oct 06 '17 at 21:42

score 2 · Answer 1 · answered Oct 06 '17 at 15:30

The fields in the SELECT don't exist before a WHERE. Take a peak at https://blog.sqlauthority.com/2009/04/06/sql-server-logical-query-processing-phases-order-of-statement-execution/.

SQL's normal processing order is:

FROM
ON
OUTER
WHERE
GROUP BY
CUBE | ROLLUP
HAVING
SELECT
DISTINCT 10 ORDER BY
TOP

So in your query, there is no [maxDate] available yet. To have one available for a WHERE, use a CTE to generate one. Then apply the WHERE.

NOTE: I used INTs for the VALUE() columns for simplicity's sake.

SQL Fiddle

MS SQL Server 2014 Schema Setup:

CREATE TABLE t (field1 varchar(10), f2 int, f3 int, f4 int ) ;

INSERT INTO t (field1, f2, f3, f4) 
VALUES ('first',10,5,0), ('second',1,7,3), ('third',2,4,6) ;

Query 1:

; WITH a AS (
  SELECT field1,
    (
      SELECT Max(v) 
      FROM (VALUES (f2), (f3), (f4)) AS value(v)
    ) AS maxF
  FROM t
)
SELECT a.field1, a.maxF
FROM a 
WHERE a.maxF = 6 ;

Results:

| field1 | maxF |
|--------|------|
|  third |    6 |

Shawn, thanks for your reply, but I guess I'm really not that well in SQL to see how I can apply this to my current query. My current query is much more complex compared to the example I provided. I have updated my SQL code to illustrate where my problem is. Thanks. — Ray Amarelo, Oct 06 '17 at 20:39
His suggestion is that you embed (as a CTE) your existing query, minus the `WHERE` clause, into another query that passes all of the data through, but applies a `WHERE`. His` maxF=6` is your `MaxDate='2016-01-01'`. — Andrew Lazarus, Oct 06 '17 at 21:40

Alberto Martinez · Accepted Answer · 2017-10-06T21:31:57.013

2

You can use CROSS APPLY for getting the maximum date instead of using a subquery in the SELECT, in this way you have the MaxDate column available for the WHERE condition:

SELECT [Other Fields], MaxDate
FROM YourTableName
CROSS APPLY (SELECT Max(v) 
  FROM (VALUES (date1), (date2), (date3), [...]) AS value(v))
  AS Dates (MaxDate)
WHERE MaxDate='2016-01-01'

edited Oct 06 '17 at 21:31

answered Oct 06 '17 at 21:25

Alberto Martinez

2,620
4
25
28

Thanks Alberto...this is exactly what I was looking for, and it works. Now I can use the WHERE condition!!!! – Ray Amarelo Oct 10 '17 at 13:25
Won't a CROSS APPLY here run for each row in the SELECT? I'd check out the query plan on this and make sure it will scale. This may get slow for a large dataset from . – Shawn Oct 10 '17 at 14:02
@Shawn I'm not sure about how `CROSS APPLY` would be processed in this situation, but the alternative is run the query directly in the `SELECT` list and that case it should be processed for each row almost sure (so in the worst case if offers the same performance). – Alberto Martinez Oct 10 '17 at 20:23

Using a SQL WHERE clause on Max value of Multiple Columns

2 Answers2