SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Evangelist Alluvian's Avatar
    Join Date
    Jan 2007
    Location
    Orlando, FL
    Posts
    417
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    return info on last record with one query

    I'm cleaning up some old clunky code of mine, but unfortunately, my skill in mySQL are also old and clunky it seems. I know I can do this, but can't figure out just how...

    Lets call the table:
    comics

    Lets call the fields I am looking for:
    ID
    filename


    I want to get the maximum ID from the table and the filename field corresponding to that maximum id.

    I can do it easy in two queries:
    select max(ID) as max_id from comics
    then in php send
    select filename from comics where ID=$id
    (where $id is the number returned from the first query)

    I would like to do ONE query like:
    Code:
    select max(ID) as max_id,
       and filename
      from comics
    where ID=max_id
    Obviously, the above does not work. Could somebody point out my mistake and show me the correct way to do it?

  2. #2
    SitePoint Enthusiast Boxer's Avatar
    Join Date
    Jan 2008
    Location
    Denver, Colorado
    Posts
    86
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code SQL:
    SELECT * FROM comics ORDER BY ID DESC LIMIT 1
    -- this will give you the entire row with the maximum ID

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Alluvian View Post
    I would like to do ONE query like:
    Code:
    select max(ID) as max_id,
       and filename
      from comics
    where ID=max_id
    Obviously, the above does not work.
    you were pretty close
    Code:
    SELECT id as max_id
         , filename
      FROM comics
     WHERE id = ( SELECT MAX(id) FROM comics )
    this will return the correct results--unlike Boxer's solution--whenever there is more than one row with the maximum id
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Evangelist Alluvian's Avatar
    Join Date
    Jan 2007
    Location
    Orlando, FL
    Posts
    417
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Woo! Answer from the master!

    Thanks to both of you for your help. I used to post and troll these boards all the time to see how rudy would answer the more convoluted questions. They always make so much sense when organized so well.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    thanks for the kind words

    obviously, i was being a little sarcastic about more than one row with the maximum id -- if id is the primary key, which is so often the case

    howver, consider the classic homework question "which employee has the greatest salary" -- in this case, ORDER BY salary DESC LIMIT 1 will not produce the correct result (although the employee thus displayed will certainly be one of the employees who have the highest salary)

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •