Thread: Re: [SQL] 7.0 weirdness
gid is unique.. it's a serial.. funny thing is tho this worked on 6.5 oh well thanks for the info. jeff On Tue, 30 May 2000, omid omoomi wrote: > Hi jeff, > I'm not sure but may be that's because you are using select distinct and so > there would be a few rows with same "gid" but different "created" fields in > your table . And PG does not know which one to select and compare for ORDER > BY clause. If that ,you would need to change the table structure to a better > normal form. > Regards , > Omid Omoomi > > > >From: Jeff MacDonald <jeff@pgsql.com> > >Reply-To: Jeff MacDonald <jeff@pgsql.com> > >To: pgsql-sql@postgresql.org, pgsql-hackers@postgresql.org > >Subject: [SQL] 7.0 weirdness > >Date: Tue, 30 May 2000 09:28:11 -0300 (ADT) > > > >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 > > > > > > > > ________________________________________________________________________ > Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com >
Hi, Jeff MacDonald: > gid is unique.. it's a serial.. > Then there is no point in using "DISTINCT" in the first place, is there? > funny thing is tho this worked on 6.5 It happened to work because your gid is unique. But in the general case, it can't work. Consider this table: gid createdX 1Y 2X 3 Now, should your query's result be gidXY or should it be gidYX ? And since the typical implementation throws away non-selected-for columns before UNIQUEing, how should it be able to sort anything? -- Matthias Urlichs | noris network GmbH | smurf@noris.de | ICQ: 20193661 The quote was selected randomly. Really. | http://smurf.noris.de/ -- Problem mit cookie: File exists
Jeff MacDonald <jeff@pgsql.com> writes:
> gid is unique.. it's a serial..
Mph. If you assume that gid is unique then the query would give
well-defined results, but if you know it's unique then why don't
you just leave off the DISTINCT?
> funny thing is tho this worked on 6.5
No, 6.5 merely failed to notice that it was giving you undefined
results.
regards, tom lane
Hi Jeff! I think you need a solution, and not explains... Tom, and the others told the truth. You missed this query. > gid is unique.. it's a serial.. I give you two ways: 1) gid __realy__ unique -> DISTINCT is unnecessary.SELECT gid FROM members -- ... etc 2) gid not unique -> DISTINCT is not enough. ;(SELECT gid,MAX(created) -- or MIN or AVG ... any aggregateFROM members --... etcGROUP BY gid ORDER BY 2; -- second colunm > > >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 best regards --nek;(
thanks for the hlep guys.. for those that are curious, the distinct is tehr cause it's someone elses code that i'm workig on .. :) have to kick out the bug's// jeff On Tue, 30 May 2000, Matthias Urlichs wrote: > Hi, > > Jeff MacDonald: > > gid is unique.. it's a serial.. > > > Then there is no point in using "DISTINCT" in the first place, is there? > > > funny thing is tho this worked on 6.5 > > It happened to work because your gid is unique. But in the general case, > it can't work. Consider this table: > > gid created > X 1 > Y 2 > X 3 > > Now, should your query's result be > > gid > X > Y > > or should it be > > gid > Y > X > > ? And since the typical implementation throws away non-selected-for > columns before UNIQUEing, how should it be able to sort anything? > > -- > Matthias Urlichs | noris network GmbH | smurf@noris.de | ICQ: 20193661 > The quote was selected randomly. Really. | http://smurf.noris.de/ > -- > Problem mit cookie: File exists >