Message-ID: <20030225082249.44019.qmail@web80406.mail.yahoo.com>
Date: Tue, 25 Feb 2003 00:22:49 -0800 (PST)
From: Eddie Cheung <vampyre5@yahoo.com>
Reply-To: vampyre5@yahoo.com
Subject: Re: Help with query involving aggregation and joining.
To: pgsql-sql@postgresql.org
In-Reply-To: <1037.219.65.254.132.1046101897.squirrel@mail.trade-india.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii


Hi,

I was very amused by Mallah's suggestion because it is so simple and yet manage to
workaround the problem I am facing. So I will be using the SQL suggested by Bruno and
wrap the query with this workaround.

Christoph, I am using version:
PostgreSQL 7.2.3 on i686-pc-cygwin, compiled by GCC 2.95.3-5
The error message appeared when the SQL statement was not wrapped by 
   "SELECT * FROM (...) AS results ORDER..."
and when the sorting is swapped to sort by history.submission first. Details can be found
in my previous email.

I have not been able to get Josh suggestion to work. While tidying the SQL statement,
there were some error messages that I didn't manage to solve. Josh's suggestion is
probably more proper, but I will use the other alternative because the syntax is easier
for me to understand. Performance is not an issue in this case because the results came
back almost immediately in my environment.

For the record, I will be using something like this:

SELECT * FROM (
 SELECT DISTINCT ON (course.id) course.id, history.id, course.name, history.submission 
 FROM history JOIN course ON history.courseId =
 course.id ORDER BY course.id, history.submission DESC
) as results ORDER BY results.submission DESC;


Thank you for all your help. I really appreciate it.

Cheers,
Eddie


--- mallah@trade-india.com wrote:
> 
> 
> sorry i did not post any sample data or definations.
> i was replying to someone else's posting
> 
> i do get such an error on pgsql 7.3.2
> 
> regds
> mallah.
> 
> 
> >>
> >> ERROR:
> >> SELECT DISTINCT ON expressions must match
> >> initial ORDER BY expressions may be gotten over by
> >> wrapping the first query result in a subselect.
> >>
> >> not sure though if its proper.
> >> regds
> >> mallah.
> >>
> >> test=# SELECT * from ( SELECT  distinct on (a.id) b.id
> >> ,courseid,name,submission   from course a join  history b on
> >> (a.id=b.courseid)  )  as results order by results.submission desc;
> >>
> > I'm currently working with
> > select version();
> >                            version
> > ---------------------------------------------------------------
> > PostgreSQL 7.2.3 on hppa-hp-hpux10.20, compiled by GCC 2.95.2
> > (1 row)
> > I've used your table definitions and sample data you've sent earlier and do not
> receive an
> > error message on the above SELECT statement. Did you?
> >
> > Regards, Christoph
> 
> 
> 
> -----------------------------------------
> Get your free web based email at trade-india.com.
>    "India's Leading B2B eMarketplace.!"
> http://www.trade-india.com/
> 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org


__________________________________________________
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com