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

Wed, 18 Jul 2001 10:40:57 +0200
Kelbert <jean-michel@club-internet.fr>
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 peop
 order by people_lastname asc limit 40 offset 0;


Wed, 18 Jul 2001 11:06:56 -0700 (PDT)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Re: ERROR: SELECT DISTINCT ON with postgresql v 7.1.2