Re: problem with distinct rows

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