Thread: ERROR: SELECT DISTINCT ON with postgresql v 7.1.2

ERROR: SELECT DISTINCT ON with postgresql v 7.1.2

From
Kelbert
Date:
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 !

Thanks

--
Jean-Michel KELBERT


Re: ERROR: SELECT DISTINCT ON with postgresql v 7.1.2

From
Stephan Szabo
Date:
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;