I have the following query to my db:
$examCnt = query("select * from apExams");
when I print that out I get:
Array
(
[0] => Array
(
[date] => 2023-08-24
[exam] => 1
[question] => 1
[active] => 0
)
[1] => Array
(
[date] => 2023-08-24
[exam] => 1
[question] => 2
[active] => 0
)
[2] => Array
(
[date] => 2023-08-25
[exam] => 3
[question] => 1
[active] => 0
)
[3] => Array
(
[date] => 2023-08-25
[exam] => 3
[question] => 2
[active] => 0
)
)
but when I run the following query:
$examCnt = query("select *, max(exam) as cnt from apExams");
I get:
Array
(
[0] => Array
(
[date] => 2023-08-24
[exam] => 1
[question] => 1
[active] => 0
[cnt] => 3
)
)
Why don't I get back all 4 records with cnt = 3?
When you don't use GROUP BY, any use of an aggregate function causes the entire table to be aggregated into one row in the result set.
To get what you want, you should use MAX(cnt) in a subquery.
SELECT *, (SELECT MAX(exam) FROM apExams) AS cnt
FROM apExams
or
SELECT *
FROM apExams
CROSS JOIN (
SELECT MAX(exam) AS cnt
FROM apExams
) AS x
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments