How can I use MAX and LIMIT together in MySQL

Question

I have a table with the following structure:

ID | Color

1 | red
2 | blue
3 | yellow
8 | purple
10| green
.
.
.
100|yellow

I would like to be able to grab the MAX ID value for the first 5 rows.

For example, I would like to do something like so: Select MAX(ID) From Table LIMIT 5

Hoping this would return the value 10

But, MySQL keeps returning 100...its like MySQL does not even see the LIMIT clause.

Answer 1

Use a subselect:

SELECT MAX(id) FROM (SELECT id FROM table LIMIT 5);

That might not quite be valid SQL, but that should give you a general idea.

Answer 2

It sounds like you want to select the top 5 records (ordered by ID) and then get the highest value of that group? If so:

SELECT
    MAX(ID)
FROM
    ( 
         SELECT ID 
         FROM YourTable
         ORDER BY ID
         LIMIT 5
    ) as T1