Question 237187 in Mysql Question posted in
The official MySQL documentation can be found here.

mysql max function only returns 1 row

I have the following query to my db:

$examCnt = query("select * from apExams");

when I print that out I get:

Array
(
[0] => Array
    (
        August 24, 2023 => 2023-08-24
        [exam] => 1
        [question] => 1
        [active] => 0
    )

[1] => Array
    (
        August 24, 2023 => 2023-08-24
        [exam] => 1
        [question] => 2
        [active] => 0
    )

[2] => Array
    (
        August 24, 2023 => 2023-08-25
        [exam] => 3
        [question] => 1
        [active] => 0
    )

[3] => Array
    (
        August 24, 2023 => 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
        (
            August 24, 2023 => 2023-08-24
            [exam] => 1
            [question] => 1
            [active] => 0
            [cnt] => 3
        )

)

Why don’t I get back all 4 records with cnt = 3?

Tags:

2

Answers


  1. 0

    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
    Login or Signup to reply.
  2. 0

    Try it with Group By as follows.

    $examCnt = query("select *, max(exam) as cnt from apExams GROUP BY id");

    The reason it returned only one row is that (*,max()) returns the maximum value in a set of values appending max(); thus, you need to use GROUP BY to retrieve each row.

    I just realised that group by cannot meet the need, definitely using subquery.

    The query result without group by

    The query result with group by

    Subquery

    Hope this can fix your issue.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search