sjw0525 sjw0525 - 17 days ago 5
MySQL Question

Can you return the max value of a column multiple times in multiple rows in a MySQL SELECT statement?

When I run the following statement:

SELECT * FROM templates ORDER BY templateID

I get the below:

+---------+-----------+-------------------+
| Template| Name | NumberOfColumns |
+---------+-----------+-------------------+
| 1 | blue | 1 |
| 2 | red | 2 |
| 3 | green | 4 |
+---------+-----------+-------------------+


And when I do the following:

SELECT *, max(NumberOfContentBlocks) FROM templates;

I get the below:

+---------+-----------+-------------------+
| Template| Name | NumberOfColumns |
+---------+-----------+-------------------+
| 1 | blue | 4 |
+---------+-----------+-------------------+


Is there a SELECT statement that will return the below?:

+---------+-----------+-------------------+
| Template| Name | NumberOfColumns |
+---------+-----------+-------------------+
| 1 | blue | 4 |
| 2 | red | 4 |
| 3 | green | 4 |
+---------+-----------+-------------------+

Answer

You can put a scalar subquery in the select-list:

SELECT Template, Name, 
  (SELECT MAX(NumberOfColumns) FROM templates) AS NumberOfColumns 
FROM templates ORDER BY templateID;

Scalar means as long as the subquery is guaranteed to return one row and one column, it's okay to put in a scalar context (like one column of the select-list).

Comments