mysql max of all records in a column

Question

I'm not sure what I'm trying to do can be accomplished. I'm using MySQL. I want my query to return for a field the value for the record or, if it's null, one more than the maximum value of that field for all records, including the records not being returned. As I read the definition of the MAX() function, I don't think it's what I need.

For example, if I have a table

GO	a
1	b
2	c
3	d
	e

and I want to return 4 for the fourth record (because GO is null for that record, and 4 is 1 more than 3, the max value for GO), and for each other record, their value for GO.

I've tried nesting Max() inside IFNULL(), but for the case of where a = e, the MAX(GO) is simply returning NULL, because all records where a=e have a NULL GO value. I guess what I'm asking for is really to place the result of a separate query inside this one:
SELECT MAX(GO) FROM [TABLE]

I guess I've oversimplified my example. My GO column is actually an auto_incremented id column, and a is a foreign key to a joined table. What I'm really looking for is if there's no record in this table to match up with the joined table's record, the next auto_increment value.

What you need to do is publish sample data and expected outcome (as text) — P.Salmon
If GO is an auto_incremented column, how come it's NULL and should it be defined as not null? — Stu

Ergest Basha Ergest Basha · Accepted Answer · 2021-12-21T16:31:00

If your MySQL supports window function you can use:

SELECT a, case when go is null then 1 + lag(go) OVER (ORDER BY a)  else go end as go
FROM test_tbl;

Result:

a go
a 1
b 2
c 3
d 4

Demo: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=9e7f54d06caa83be71bad3b2f364ece5

mysql max of all records in a column

2 Answers