Stucked up with MySQL

[atitthaker]

I didn't find anyother place to post the query so I am posting it here.

This is my table like:
ID name
1  t1
3  t2
5  t3
7  t4
2  t5

Now I want to find out the row with 3rd maximum ID. In this case the raw with id 3.
I tried many ways but I am unable to get work arround this. MySQL is not supporting IN construct with my version.




Plz Help

Thanks

[fenway]

First, all versions support the IN clause.  Second, why do you ever want to 3rd largest ID?  Third, the following should work:

[code]
SELECT * FROM yourTable ORDER BY ID LIMIT 3,1
[/code]

Assuming that there are at least 3 entries in your table.

[extrovertive]

SELECT `ID`, `name`
FROM t
ORDER BY ID ASC
LIMIT 2, 1

[extrovertive]

I'm also doing an experiement.

How do I increment the max ID value in the table.

I tried:

UPDATE t
SET ID = ID + 1
WHERE ID = (SELECT MAX(ID)  FROM t)

but i get

#1093 - You can't specify target table 't' for update in FROM clause

[AndyB]

Off the top of my head, I'd say the error message you reported is telling you exactly what the problem is ... and the fix shouldn't be far away from reading that message.

[fenway]

Not that you're ever supposed to, but you can just "SET" the auto_increment value directly.