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;

POPULAR ONLINE

In which template event can I hide elements from the main template (Meteor)?

JQuery Mobile panel - lost content calling .trigger(“create”)

MYSQL: Select data from different tables limit 10, while the other table limit 5

How to select combination of 4 digits number from table columns?

I have a table as below:-

150

MySQL

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

MySQL

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

MySQL

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