How can I use MAX and LIMIT together in MySQL

Question

I have a table with the following structure:

ID | Colour

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

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

For example, I would like to do something like this: Select MAX (ID) from the LIMIT 5 table

Hoping this returns a value of 10

But MySQL keeps returning 100 ... it looks like MySQL doesn't even see the LIMIT clause.

Answer 1

Looks like you want to select the 5 best entries (sorted by ID) and then get the highest value of this group? If yes:

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

Answer 2

There is a better way to do this than a subquery:

 SELECT id FROM table LIMIT 4,1 

Get one row, but skip first. 4. Add 'order by id' if it is a MyISAM table.

Answer 3

Use the subquery:

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

This may not be entirely correct SQL, but it should give you a general idea.

Answer 4

If you have a table with the following structure

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

The MAX ID value for the first 5 (AUTO_INCREMENT?) Values ​​is 5. Btw, the MAX ID value for the first 6 values ​​is 6. And 100 for all values. Best wishes.