Re: problem with distinct rows

Add a SORTNAME field to the artist table and use that for sorting. This
will help you deal with diacrtics and accented characters by transposing
them to a regular character instead.

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
>
>