7.0 weirdness
hi folks,
this query works fine in 6.5 but screwie in 7.0
7.0
gm=> SELECT DISTINCT gid FROM members
gm-> WHERE active = 't'
gm-> AND (gender = 0
gm-> AND (wantrstypemale LIKE '%Short Term%'
gm-> OR wantrstypemale like '%Marriage%'
gm-> OR wantrstypemale like '%Long Term%'
gm-> OR wantrstypemale like '%Penpal%'
gm-> OR wantrstypemale like '%Activity Partner%')
gm-> ) order by created desc;
ERROR: For SELECT DISTINCT, ORDER BY expressions must appear in target list
gm=>
any idea's ?
jeff
It seems to me that it was lack of control in 6.5 version...
For one "gid", you may have several "created" values, so Postgres is not
able to decide which value must be taken and ordered
Simple example
gid created
1 1
1 3
2 2
In which order is Postgres supposed to give the data???
Patrick Fiche
-----Message d'origine-----
De : pgsql-sql-owner@hub.org [mailto:pgsql-sql-owner@hub.org]De la part
de Jeff MacDonald
Envoy� : mardi 30 mai 2000 14:28
� : pgsql-sql@postgresql.org; pgsql-hackers@postgresql.org
Objet : [SQL] 7.0 weirdness
hi folks,
this query works fine in 6.5 but screwie in 7.0
7.0
gm=> SELECT DISTINCT gid FROM members
gm-> WHERE active = 't'
gm-> AND (gender = 0
gm-> AND (wantrstypemale LIKE '%Short Term%'
gm-> OR wantrstypemale like '%Marriage%'
gm-> OR wantrstypemale like '%Long Term%'
gm-> OR wantrstypemale like '%Penpal%'
gm-> OR wantrstypemale like '%Activity Partner%')
gm-> ) order by created desc;
ERROR: For SELECT DISTINCT, ORDER BY expressions must appear in target list
gm=>
any idea's ?
jeff
gid is unique..
jeff
On Tue, 30 May 2000, Patrick FICHE wrote:
Show quoted text
It seems to me that it was lack of control in 6.5 version...
For one "gid", you may have several "created" values, so Postgres is not
able to decide which value must be taken and orderedSimple example
gid created
1 1
1 3
2 2In which order is Postgres supposed to give the data???
Patrick Fiche
-----Message d'origine-----
De : pgsql-sql-owner@hub.org [mailto:pgsql-sql-owner@hub.org]De la part
de Jeff MacDonald
Envoy��� : mardi 30 mai 2000 14:28
��� : pgsql-sql@postgresql.org; pgsql-hackers@postgresql.org
Objet : [SQL] 7.0 weirdnesshi folks,
this query works fine in 6.5 but screwie in 7.0
7.0
gm=> SELECT DISTINCT gid FROM members
gm-> WHERE active = 't'
gm-> AND (gender = 0
gm-> AND (wantrstypemale LIKE '%Short Term%'
gm-> OR wantrstypemale like '%Marriage%'
gm-> OR wantrstypemale like '%Long Term%'
gm-> OR wantrstypemale like '%Penpal%'
gm-> OR wantrstypemale like '%Activity Partner%')
gm-> ) order by created desc;
ERROR: For SELECT DISTINCT, ORDER BY expressions must appear in target list
gm=>any idea's ?
jeff