Jump to content


Photo

Stucked up with MySQL


  • Please log in to reply
5 replies to this topic

#1 atitthaker

atitthaker
  • Members
  • PipPipPip
  • Advanced Member
  • 52 posts
  • LocationBanglore, India

Posted 29 August 2006 - 06:58 AM

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
Beginner with PHP and need help

#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 29 August 2006 - 02:34 PM

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

SELECT * FROM yourTable ORDER BY ID LIMIT 3,1

Assuming that there are at least 3 entries in your table.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#3 extrovertive

extrovertive
  • Members
  • PipPipPip
  • Advanced Member
  • 235 posts

Posted 31 August 2006 - 11:48 PM

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

#4 extrovertive

extrovertive
  • Members
  • PipPipPip
  • Advanced Member
  • 235 posts

Posted 01 September 2006 - 12:01 AM

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



#5 AndyB

AndyB
  • Staff Alumni
  • Advanced Member
  • 5,465 posts
  • LocationToronto

Posted 01 September 2006 - 02:01 AM

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.
Legend has it that reading the manual never killed anyone.
My site

#6 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 01 September 2006 - 02:12 PM

Not that you're ever supposed to, but you can just "SET" the auto_increment value directly.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users