The sortname value for Genée would be "genee".
With a few changes to your data model (and possibly application) you will probably end up making your life a little easier.
John Sidney-Woollett
Martijn van Oosterhout wrote:
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
---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to majordomo@xxxxxxxxxxxxxx
