why `if(id=max(id), id, id+1)` doesn't work as expected in mysql

133
November 03, 2019, at 8:50 PM

in the follow table People:

id name
 1 James
 2 Yun
 3 Ethan

if I want to find the max id I can run this query

select max(id) id from People;

the result is

id
 3 

if I want to increase id by 1 for all rows except the last one, I tried this:

select if(id=max(id), id, id+1) id, name from People

surprisingly, I got

why max() doesn't work as previous case? and why there is only one row (looks like only the 1st row) returned? I think I know how to fix this, as below:

select if(id=lastID, id, id+1) id,, name from People,
(select max(id) lastID from People) People_max
order by id;

then I got what I expected:

But I think I still don't know why the previous one doesn't work.

Thanks

Answer 1

You need to use a subquery to obtain the max value:

SELECT
    IF(id = (SELECT MAX(id) FROM People), id, id+1) id,
    name
FROM People
ORDER BY id;

When you were referring to MAX(id) in your original single-level query, you were telling MySQL to execute the query in table level aggregation mode. This means it will return just a single record representing an aggregate of the entire table. By switching to a subquery, it behaves the way you want. By the way, if you are using MySQL 8+, then we can use analytic functions here to make the query a bit less verbose:

SELECT
    IF(id = MAX(id) OVER (), id, id+1) id,
    name
FROM People
ORDER BY id;
Rent Charter Buses Company
READ ALSO
Calculate monthly average

Calculate monthly average

I have a table which is recording utilization of a productIn logistics, there's a parameter called commodity average monthly utilization (CAMU) this keep track of your monthly utilization to date

146
Create Archive section count and group by month and year [on hold]

Create Archive section count and group by month and year [on hold]

I want to make archive that counts number of posts and order it by month and year in PHPFor date & time i use PHP command time() and that is how it is stored in mysql database

125
Insert several rows into MySQL table at once

Insert several rows into MySQL table at once

First of all I have to say that I am new on PHP or any backend language, so I'm sorry in advance if by any chance my question seems silly

105