Select max value under a specified value in mysql

Question

I am looking for a way to select the max id value in a table under a specified value. I am trying to use the following query

select max(id) as 'maxid' from proveedor where 'id' < 6666

The value I am expecting is 3, but instead I get 7777. Is there a different way to do this?

+-------+
|   id  |
+-------+
| 1     |
| 2     |
| 3     |
| 6666  |
| 7777  |
+-------+

Answer 1

You have an typo in your query:

where 'id' < 6666

Should be(without the quotes):

where id < 6666

So the following works as expected:

select max(id) as 'maxid' from proveedor where id < 6666

Demo: SQLFiddle demo

Answer 2

Remove the single quote marks from the 'id'. With the single quote marks, MySQL treats it as a string instead of a column. If your column names contain special characters you can use backticks.

select max(id) as 'maxid' from proveedor where id < 6666

Answer 3

Your id would appear to be stored as a string. If you like, you can convert to a number:

select max(id + 0) as maxid
from proveedor
where (id + 0) < 6666;

Note that you have misused single quotes in your sample code. I don't believe this is the root cause of your problem. Nevertheless, you should learn how to use quotes correctly in SQL.

If you want the value as a string, you can do:

select id as maxid
from proveedor
where (id + 0) < 6666
order by (id + 0) desc
limit 1;