Search code examples
mysqlsqlsyntaxwhere-clause

Use result of WITH clause to filter WHERE ... IN columnName

I want to use the result of a WITH clause to filter a query like below:

WITH Max_Dates AS (
    SELECT MAX(created_date) AS maxdate
    FROM transactions
    GROUP BY DATE (created_date)
)
SELECT *
FROM transactions
WHERE created_date IN Max_Dates -- can I somehow reference column maxdate as a list here?

The syntax is incorrect. I know I can use the content of the WITH clause as a subquery in the WHERE below to get the result I want, but I want to know specifically if the WITH result can be used.

My question is, is there a general syntax for using a column from a WITH clause to filter as a list in a WHERE ... IN {list}?

As per this blog it seems it should be possible to use WHERE created_at IN Max_Dates.maxdate, but I'm using MySQL 8.0.29 and it doesn't like that syntax - Error: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Max_Dates.maxdate' at line 8")

Solution 
  • WITH Max_Dates AS 
(
  SELECT MAX(created_date) AS maxdate
  FROM transactions
  GROUP BY DATE (created_date)
)
SELECT *
FROM transactions
WHERE created_date IN (select maxdate from Max_Dates)

    The CTE (Common Table Expression) Max_Dates is a resultset that potentially has multiple columns, so you must specify the specific column from Max_Dates that should be used to build the list of values for the IN expression.