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, titleBTW, 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
---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your message can get through to the mailing list cleanly
