SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Enthusiast
    Join Date
    May 2007
    Posts
    56
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    find the first value

    Hi All

    I am trying to complete this select query.

    SELECT photo_name, photo_show, photo_description, photo_filename FROM pdetails_photos WHERE photo_category='would be the first value of the photo_catergory' ORDER BY photo_name ASC

    Not sure what to put there as people can delete a photo_category so using value such as '1' would not work cos it could be deleted by the user.

    Is this possible??

    Thanks

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    what characteristic of a photo category would you use to distinguish an early category from a later category?

    maybe you can do a SHOW CREATE TABLE so we can see the actual table layout
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    May 2007
    Posts
    56
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi
    Here is table format:

    CREATE TABLE pdetails_category (
    category_id bigint(20) unsigned NOT NULL auto_increment,
    category_name varchar(50) NOT NULL default '0',
    PRIMARY KEY (category_id),
    KEY category_id (category_id)
    ) TYPE=MyISAM;


    CREATE TABLE pdetails_photos (
    photo_id bigint(20) unsigned NOT NULL auto_increment,
    photo_filename varchar(25),
    photo_name varchar(100),
    photo_show varchar(100),
    photo_description text,
    photo_category bigint(20) unsigned NOT NULL default '0',
    PRIMARY KEY (photo_id),
    KEY photo_id (photo_id)
    ) TYPE=MyISAM;

    the category_id would link to the photo_category, but if the user deteled the category for whatever reason then the first category would change.

    what i am trying to do is order the pdetails_photos table by the photo_name and also order the pdetails_category table by the category_name and select the first set of photo_category order by there category_name, there name would be ordered in ASC, if that makes any sense.

    I think i would have to link the two tables together??

    Thanks for your help.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT pdetails_photos.photo_name
         , pdetails_photos.photo_show
         , pdetails_photos.photo_description
         , pdetails_photos.photo_filename 
      FROM pdetails_category 
    INNER
      JOIN pdetails_photos 
        ON pdetails_photos.photo_category = pdetails_category.category_id 
     WHERE pdetails_category.category_name = 
           ( SELECT MIN(category_name)
               FROM pdetails_category )
    ORDER 
        BY pdetails_photos.photo_name ASC
    by the way, unless you are expecting over 4 billion photos or categories, you should really use something smaller than BIGINT

    also, declaring a KEY for a column that is already defined as a PRIMARY KEY is redundant and inefficient
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast
    Join Date
    May 2007
    Posts
    56
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for your help!!!!

    I know i am not using best practice rules but i am new to this.

    Once again thanks for all your help.


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
  •