Re: Help with query involving aggregation and joining. - Mailing list pgsql-sql
From | Eddie Cheung |
---|---|
Subject | Re: Help with query involving aggregation and joining. |
Date | |
Msg-id | 20030225082249.44019.qmail@web80406.mail.yahoo.com Whole thread Raw |
In response to | Re: Help with query involving aggregation and joining. (<mallah@trade-india.com>) |
Responses |
Re: Help with query involving aggregation and joining.
|
List | pgsql-sql |
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 courseON 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