problem with distinct rows - Mailing list pgsql-general

From tony
Subject problem with distinct rows
Date
Msg-id 1110272629.3566.22.camel@hush
Whole thread Raw
Responses Re: problem with distinct rows  (John Sidney-Woollett <johnsw@wardbrook.com>)
Re: problem with distinct rows  (Martijn van Oosterhout <kleptog@svana.org>)
List pgsql-general
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




pgsql-general by date:

Previous
From: "Net Virtual Mailing Lists"
Date:
Subject: Re: Inherited tables, triggers, and schemas...
Next
From: Stephane Bortzmeyer
Date:
Subject: Re: [Auth] 'ident' method and LDAP user accounts