Order by attribute in child table

Hi all,

I've been thinking about it, and I'm a bit stumped, and wondered if
anyone here could help.

I have two tables, an albums and a photos table. An album contains
many photos. I want to get a list of albums ordered by the most
recent photo in the album. Basically, I want to be able to order
albums by the latest creation date of a photo in the album.

How would one go about doing that? I was thinking a correlated
subquery of some sort, but I've never have seen an example to use it
right after ORDER BY and I can't seem to get it to work myself.

As an alternative, perhaps it would be easier to update a new field in
the albums table called "last_uploaded_on" that gets timestamped on
every upload, and "order by" on that?

Thanks in advance.

Wilhelm wrote:
> Hi all,
>
> I've been thinking about it, and I'm a bit stumped, and wondered if
> anyone here could help.
>
> I have two tables, an albums and a photos table. An album contains
> many photos. I want to get a list of albums ordered by the most
> recent photo in the album. Basically, I want to be able to order
> albums by the latest creation date of a photo in the album.
>
> How would one go about doing that? I was thinking a correlated
> subquery of some sort, but I've never have seen an example to use it
> right after ORDER BY and I can't seem to get it to work myself.
>
> As an alternative, perhaps it would be easier to update a new field in
> the albums table called "last_uploaded_on" that gets timestamped on
> every upload, and "order by" on that?
>
> Thanks in advance.
>
>

The CREATE TABLE statements and sample data would be of great help in
your query. Otherwise we can only guess.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

On 6 May, 23:58, Wilhelm <iam...@gmail.com> wrote:
> Hi all,
>
> I've been thinking about it, and I'm a bit stumped, and wondered if
> anyone here could help.
>
> I have two tables, an albums and a photos table. An album contains
> many photos. I want to get a list of albums ordered by the most
> recent photo in the album. Basically, I want to be able to order
> albums by the latest creation date of a photo in the album.
>
> How would one go about doing that? I was thinking a correlated
> subquery of some sort, but I've never have seen an example to use it
> right after ORDER BY and I can't seem to get it to work myself.
>
> As an alternative, perhaps it would be easier to update a new field in
> the albums table called "last_uploaded_on" that gets timestamped on
> every upload, and "order by" on that?
>
> Thanks in advance.

You would do it using a JOIN (not a correlated sub-query). However as
Jerry points out, without further info it is difficult to be more
precise in our advice.