#1
  1. No Profile Picture
    frequent PHP idiot
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2003
    Posts
    354
    Rep Power
    12

    Get highest version of asset


    I have a table which stores a row ID, an image URL and a version number of of that image URL. The way it works is, you add a project with images. Then you can add multiple versions of each image. The problem I'm running into is how to display the highest version number of that image rather than showing all images of a project.

    Does that explanation make sense? Basically i need some way to pull all the various versions of images associated with the project and then only show the most current version of each image. Whats the best way to do this?
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Posts
    194
    Rep Power
    77
    In the MySQL query for the images at the end add
    Code:
    ORDER BY DESC LIMIT 0,1
  4. #3
  5. Sarcky
    Devshed Supreme Being (6500+ posts)

    Join Date
    Oct 2006
    Location
    Pennsylvania, USA
    Posts
    10,908
    Rep Power
    6352
    That ORDER BY is incorrect and doesn't really help you.

    I'm moving this to thread to the MySQL forum as it has nothing to do with PHP. I assume mixing MAX() and GROUP BY would work, but this may be incorrect since I haven't actually written a raw query in a while:

    Code:
    SELECT row_id, image_id, MAX(version_no) FROM your_table GROUP BY image_id;
    HEY! YOU! Read the New User Guide and Forum Rules

    "They that can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety." -Benjamin Franklin

    "The greatest tragedy of this changing society is that people who never knew what it was like before will simply assume that this is the way things are supposed to be." -2600 Magazine, Fall 2002

    Think we're being rude? Maybe you asked a bad question or you're a Help Vampire. Trying to argue intelligently? Please read this.
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    Originally Posted by soupman99
    Whats the best way to do this?
    please do a SHOW CREATE TABLE and ask your question again, indicating which columns you'd like returned
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo