Re: ERROR: SELECT DISTINCT ON with postgresql v 7.1.2

On Wed, 18 Jul 2001, Kelbert wrote:

> Hello,
> 
> I have a problem white one sql request. I got this error message :
> 
> Warning: PostgreSQL query failed: ERROR: SELECT DISTINCT ON expressions
> must match initial ORDER BY expressions in
> /export/castor-b7/local-home/kelbertj/Prog/web/lumiere/admin/recherche_realisateurs.php
> on line 85 ERROR: SELECT DISTINCT ON expressions must match initial
> ORDER BY expressions SELECT DISTINCT ON (people_id)
> people_id,people_lastname,people_firstname from people where
> lower(people_firstname) ~* (SELECT text_accents('\\\"Luc\\$')) order by
> people_lastname ASC limit 40 offset 0
> 
> I didn't find any solution to this problem ! If you have any idea I'll
> be most gratefull If you could answer !

First a warning. The query you've written is potential non-deterministic
if you have a people_id that has multiple rows with different last names
that meet the where clause.  This is why the query was rejected in the
first place.  The ordering that the rows got chosen (semi-random) would
determine which last name was used and could change the output.

If you *really* want to do this, you can probably put the select distinct
on in a subquery (basically untested, so there might be some syntax
errors)...
select people_id, people_lastname, people_firstname from ( select distinct on (people_id) people_id, people_lastname,
people_firstname from people where lower(people_firstname) ~*   (Select text_accents('\\\"Luc\\$')) ) as peoporder by
people_lastnameasc limit 40 offset 0;