On Oct 21, 5:44*am, strawberry <zac.ca... (AT) gmail (DOT) com> wrote:
thanks for the confirmation. The display order will change eventually
with time... some will remain with the default value of '1'. At least,
in the case there is a different display order, only one track will
show up... If the display order is the same for many tracks, i don't
care wich one the system picks... is it for me to make sure the
display order is entered correctly.

Also, I will try and change the WHERE condition to an ON condition...

I didn't try the other solutions... i'm trying one by one until i get
to understand how every solution works. I will try solution #3 this
afternoon and post the results...

Thanks again

Thinking about it further, I see there's a flaw in solutions 1 & 3

The full specification (as I understand it) reads like this:

Give me a list of all distinct tracks, their associated albums, the
album release dates, and album artwork.
In the event that a track exists on more than one album, give me only
the album and release date of the earliest release.
In the event that a track exists on two or more albums which sharing
identical earliest release dates, give me the album with the lowest
display order.
In the event that a track exists on no albums, just give me the track.

As far as I can tell, query 2 (the 'hack') fulfils all these criteria,
but let me know if there's still a problem.

On Oct 21, 1:15*pm, strawberry <zac.ca... (AT) gmail (DOT) com> wrote:
Bingo! that is precisely the spec for this problem. Very well
explained... i kept it just in case i will needed again... Thank you.
I've tried the second query, and it outputs 447 tracks...that is the
magic number! I've checked to make sure and that one works perfectly.
Thanks for that.

My only concern is the time of execution: an average of 0.52 sec.

If i look at the first solution (the one that almost worked), that one
had an average of execution of 0.10 sec.

Just for fun, the solution #3 had an average execution time of 1.52
sec.

In conclusion, the solution #2 is the one that works and with an
acceptable execution time of 0.5 sec.

Now that i understand the concept of the 3 solutions, i'm going to try
to play around , especially with solution #1, to see if i can tweek it
and make the execution time go down a little bit.

That being said, i want to thank you for your dedication. In the
process i've learn a lot about queries.


Many thanks
Marco