find the first value

[skeej]

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

[r937]

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

[skeej]

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.

[r937]

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

[skeej]

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.