Selecting a filename column by MIN sortorder column rather than id

Dev Shed Forums Sponsor:

Stop making mediocre tutorials.The best tutorials are video! Camtasia Studio makes it easy to create engaging, buzz-building screen videos at any size, in any popular format. Download the free trial!

May 3rd, 2008, 08:29 PM

LeadingZero

Registered User

Join Date: May 2008

Posts: 2

Time spent in forums: 2 h 13 m 44 sec
Reputation Power: 0

Selecting a filename column by MIN sortorder column rather than id

I'm setting up an image gallery. On the first page of the gallery's frontend I want want a repeat region that displays a list of album titles, album descriptions and the first thumbnail images associated with each given album as specified by the image's sortorder_img column (not the id). Unfortunately, I've been unable to write an SQL statement that returns the results I need and so I'm reaching out for some assistance.

Here are the tables:

photos_alb

Code:

id_alb           int(11)   auto_increment
title_alb        varchar(100)
description_alb  varchar(255)
sortorder_alb    int(11)

photos_img

Code:

id_img         int(11)   auto_increment
idalb_img      int(11)   this is the foreign key to photos_alb
filename_img   varchar(100)
caption_img    varchar(255)
sortorder_img  int(11)  incremented +1 on insert, remains unique when rearranged

The SQL of the recordset:

Code:

SELECT photos_alb.*, photos_img.*
FROM photos_alb, photos_img
WHERE photos_alb.id_alb=photos_img.idalb_img
GROUP BY sortorder_alb ASC

This allows me to create the repeat region with the album titles, album descriptions and thumbnails. However, the thumbnail filename_img is of course pulled from the first id_img encountered.

I imagine that there's some way to incorporate the MIN() function into this to return the lowest numbered 'sortorder_img' and somehow use that to return the intended 'filename_img'. Can anyone provide any insight on how I might achieve this?

Any help would be appreciated!

P.S.
If MIN(photos_img.filename_img) AS filename_img would reliably return the lowest numbered filename, I think I'd be good to go. However my image filenames are as follows: 1.jpg, 2.jpg, 3.jpg, etc. And the MIN() function doesn't appear to evaluate non numeric strings predictably.

May 3rd, 2008, 08:55 PM

r937

SQL Consultant

Dev Shed God 24th Plane (16500 - 16999 posts)

Join Date: Feb 2003

Location: Toronto Canada

Posts: 16,727

Time spent in forums: 1 Month 3 Weeks 2 Days 19 h 22 m 42 sec
Reputation Power: 848

Quote:

Originally Posted by LeadingZero

And the MIN() function doesn't appear to evaluate non numeric strings predictably.

of course it does -- it is the collating order of strings

so you would have 1.jpg, 10.jpg, 11.jpg, 12.jpg, 2.jpg, 3.jpg, ...

__________________
r937.com | rudy.ca

May 3rd, 2008, 09:01 PM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 16,727

Time spent in forums: 1 Month 3 Weeks 2 Days 19 h 22 m 42 sec
Reputation Power: 848

Code:

SELECT photos_alb.*
     , photos_img.*
  FROM photos_alb
INNER
  JOIN ( SELECT idalb_img
              , MIN(sortorder_img) AS min_sortorder
           FROM photos_img
         GROUP 
             BY idalb_img ) as mins
    ON mins.idalb_img = photos_alb.id_alb
INNER
  JOIN photos_img
    ON photos_img.idalb_img = photos_alb.id_alb
   AND photos_img.sortorder_img = mins.min_sortorder

by the way, your convention of appending the table suffix to the end of each column name is unnecessary, redundant, and mildly annoying

no offence, though, okay?

your queries would just be a lot cleaner without that

May 3rd, 2008, 10:03 PM

LeadingZero

Registered User

Join Date: May 2008

Posts: 2

Time spent in forums: 2 h 13 m 44 sec
Reputation Power: 0

Quote:

Originally Posted by r937

of course it does -- it is the collating order of strings

so you would have 1.jpg, 10.jpg, 11.jpg, 12.jpg, 2.jpg, 3.jpg, ...

Ahh, I see. That does make perfect sense.

Quote:

Originally Posted by r937

Code:

SELECT photos_alb.*
     , photos_img.*
  FROM photos_alb
INNER
  JOIN ( SELECT idalb_img
              , MIN(sortorder_img) AS min_sortorder
           FROM photos_img
         GROUP 
             BY idalb_img ) as mins
    ON mins.idalb_img = photos_alb.id_alb
INNER
  JOIN photos_img
    ON photos_img.idalb_img = photos_alb.id_alb
   AND photos_img.sortorder_img = mins.min_sortorder

Huge thanks!
I've been struggling with this for the last day and a half I'm ashamed to say. This query was the final holdup keeping me from finishing this gallery. Now, I'm going to celebrate by studying what you've so kindly contributed to learn from it.

As for the naming convention I've used, I agree that it's redundant. I initially based this project off of a tutorial created by Adobe specifically for ADDT. I used the sample database that they provided and didn't simplify the field names as I should have.

Again, thank you for your assistance.

Viewing: Dev Shed Forums > Databases > MySQL Help > Selecting a filename column by MIN sortorder column rather than id

« Previous Thread | Next Thread »

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread:

Posting Rules
You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off

View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox

Forum Jump

Free IT White Papers!

Accelerating Trading Partner Performance
One in five. That's how many partner transactions have at least one error. That is an amazing statistic, particularly given the extraordinary leaps in innovation across the global supply chain during the past two decades. Download this white paper to learn more.

Competing on Analytics
This Tech Analysis is designed to help identify characteristics shared by analytics competitors, and includes information about 32 organizations that have made a commitment to quantitative, fact-based analysis.

Cost Effective Scaling with Virtualization and Coyote Point Systems
An overview of the industry trend toward virtualization, how server consolidation has increased the importance of application uptime and the steps being taken to integrate load balancing technology with virtualized servers.

Five Checkpoints to Implementing IP Telephony
Implementation planning for IP PBX software and IP telephony has become vital as businesses replace discontinued legacy PBX phone systems. This informative whitepaper outlines five "checkpoints" for any implementation plan that will help make IP communications a successful proposition.

Hosted Email Security: Staying Ahead of New Threats
In the last two years, email has become a fierce battleground between the nefarious forces of spam and malware, and the heroes of messaging protection. The spam volumes increased alarmingly every month, bringing clever new forms of phishing and virus propagation attacks.

More Free IT White Papers!