If the created_by table includes an artist number/position to indicate
the first, second artist etc, eg
create table created_by (
work_id integer,
artist_id integer,
position integer,
primary key (work_id, artist_id, position)
);
then you can simply use the following query
select a.artist_name, w.title, w.inventory_number
from artist a, works w, created_by cb
where w.work_id = cb.work_id
and a.artist_id = cb.artist_id
and cb.position = 1
order by a.artist_name, w.title;
If you don't have a position or similar field in the created_by table,
you will have more difficulty as you're finding.
An alternative approach is to create a function which arbitrarily
returns one artist name for a work, and then sort on that but it wont be
as efficient.
select artist_name, title, inventory_number from (
select GetArtistName(w.inventory_number) as artist_name, w.title,
w.inventory_number
from works w
) as t
order by artist_name, title
BTW, I haven't checked any of this, but hopefully it will give you some
pointers or ideas.
John Sidney-Woollett
tony wrote:
> Hello,
>
> I am having a problem with returning distinct rows this is probably a
> newbie question but here goes:
>
> Tables are artist, created_by and works
> the join is on created_by.work_id and created_by.artist_id
>
> A work of art can have two or more artists listed as creators in created
> by. In the inventory lists we don't care we only need one reference to
> each work AND (this is the part that hurts) they must be ordered
> alphabetically by the _first_ artists name.
>
> example:
> artist_name : title : inventory_number
> Bernadette Genée et Alain Le Borgne : Pièce à conviction : 004090101
>
> Should be after "F" and before "H"
>
> But if I do DISTINCT ON inventory_number I must order by
> inventory_number then artist_name which totally defeats my purpose. I
> have also played with GROUP BY and HAVING which
>
> Clues much appreciated
>
> Tony Grant
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
> joining column's datatypes do not match
