You can put the DISTINCT ON() query as a subquery, and an ORDER BY in
the outer query.
Sorting by surname is tricky since you need to tell the computer how to
find it...
Hope this helps,
On Tue, Mar 08, 2005 at 10:03:48AM +0100, 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
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.