From: Rajesh Kumar Mallah <mallah@trade-india.com>
To: vampyre5@yahoo.com
To: pgsql-sql@postgresql.org
Subject: Re: Help with query involving aggregation and joining.
Date: Mon, 24 Feb 2003 18:54:21 +0530
Message-ID: <200302241854.21761.mallah@trade-india.com> (raw)
In-Reply-To: <20030224051836.25599.qmail@web80406.mail.yahoo.com>
References: <20030224051836.25599.qmail@web80406.mail.yahoo.com>
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;
+----+----------+-----------+------------+
| id | courseid | name | submission |
+----+----------+-----------+------------+
| 3 | 104 | Maths | 2002-04-30 |
| 2 | 102 | Chemistry | 2002-02-17 |
| 1 | 101 | Physics | 2002-01-20 |
+----+----------+-----------+------------+
(3 rows)
On Monday 24 February 2003 10:48 am, Eddie Cheung wrote:
> Hi,
>
> I was very glad to see the replies from you guys this
> morning. The two suggested SQL queries did not return
> the expected results, but will help me to explain the
> problem I am facing further.
>
> 1) Josh suggested the following query. (I have made
> minor changes by adding the table name to each field)
>
> SELECT history.id, history.courseId, course.name,
> MAX(history.submission) AS submission
> FROM history JOIN course ON history.courseId =
> course.Id
> GROUP BY history.id, history.courseId, course.name
> ORDER BY course.name;
>
> The results returned are:
> id | courseid | name | submission
> ----+----------+-----------+------------
> 2 | 102 | Chemistry | 2002-02-17
> 4 | 102 | Chemistry | 2002-02-22
> 3 | 104 | Maths | 2002-04-30
> 5 | 104 | Maths | 2002-03-15
> 6 | 104 | Maths | 2002-01-21
> 1 | 101 | Physics | 2002-01-20
>
> There are duplicate courses because the history.id
> column has different values. The history.id cannot be
> use in the GROUP BY clause. But it is one of the
> displaying field, so I could not remove it from the
> GROUP BY clause either.
>
> 2) Bruno suggested the following query:
>
> select distinct on (course.courseid)
> history.id, course.courseid, course.name,
> history.submission
> from course natural join history
> order by course.courseid, history.submission desc;
>
> I have not used NATURAL JOIN before, but from what I
> know, it joins the columns with the same name. Since
> the joining columns of History and Course have
> different names, I have replace JOIN clause. Please
> let me know if I have made a mistake.
>
> The modified query is:
> 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;
>
> The results returned are :
> id | id | name | submission
> -----+----+-----------+------------
> 101 | 1 | Physics | 2002-01-20
> 102 | 4 | Chemistry | 2002-02-22
> 104 | 3 | Maths | 2002-04-30
>
> The problem here is that the results are not ordered
> by the submission date. If I sort by
> "history.submission" first, I get
> ERROR: SELECT DISTINCT ON expressions must match
> initial ORDER BY expressions.
> Please note that I cannot select distinct on the
> course.name either because it is NOT unique. The
> original tables are much larger, and the only unique
> column is the id.
>
> I have included the queries to create the tables here.
>
> ------------------------
>
> CREATE TABLE course (
> id integer,
> name varchar(32),
> Constraint course_pkey Primary Key (id)
> );
>
> CREATE TABLE history (
> id integer NOT NULL,
> courseid integer REFERENCES course(id),
> submission date,
> Constraint history_pkey Primary Key (id)
> );
>
> INSERT INTO course (id,name) VALUES (101,'Physics');
> INSERT INTO course (id,name) VALUES (102,'Chemistry');
> INSERT INTO course (id,name) VALUES (103,'Biology');
> INSERT INTO course (id,name) VALUES (104,'Maths');
> INSERT INTO course (id,name) VALUES (105,'English');
>
> INSERT INTO history (id,courseid,submission) VALUES
> (1,101,'2002-01-20');
> INSERT INTO history (id,courseid,submission) VALUES
> (2,102,'2002-02-17');
> INSERT INTO history (id,courseid,submission) VALUES
> (3,104,'2002-04-30');
> INSERT INTO history (id,courseid,submission) VALUES
> (4,102,'2002-02-22');
> INSERT INTO history (id,courseid,submission) VALUES
> (5,104,'2002-03-15');
> INSERT INTO history (id,courseid,submission) VALUES
> (6,104,'2002-01-21');
>
> --------------------------------
>
>
> Thanks for all your help.
>
>
> Regards,
> Eddie
>
> --- Bruno Wolff III <bruno@wolff.to> wrote:
> > On Sun, Feb 23, 2003 at 11:02:27 -0800,
> >
> > Eddie Cheung <vampyre5@yahoo.com> wrote:
> > > HISTORY
> > > =======
> > > id | courseId | submission
> > > ---+-----------+-------------
> > > 1 | 101 | 2002-01-20
> > > 2 | 102 | 2002-02-17
> > > 3 | 104 | 2002-04-30
> > > 4 | 102 | 2002-02-22
> > > 5 | 104 | 2002-03-15
> > > 6 | 104 | 2002-01-21
> > >
> > >
> > > COURSE
> > > ======
> > > id | name
> > > ------+-----------
> > > 101 | Physics
> > > 102 | Chemistry
> > > 103 | Biology
> > > 104 | Maths
> > > 105 | English
> > >
> > >
> > > Basically I would like to display the latest
> > > submission for each course in a table as shown
> >
> > below,
> >
> > > order by name of the courses.
> > >
> > > Query Results:
> > > ==============
> > > id | courseId | name | submission
> > > ---------------------------------------
> > > 4 | 102 | Chemisty | 2002-02-22
> > > 3 | 104 | Maths | 2002-04-30
> > > 1 | 101 | Physics | 2002-01-20
> >
> > I think you want to do something like:
> >
> > select distinct on (course.courseid)
> > history.id, course.courseid, course.name,
> > history.submission
> > from course natural join history
> > order by course.courseid, history.submission desc;
> >
> > ---------------------------(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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
--
Regds
Mallah
----------------------------------------
Rajesh Kumar Mallah,
Project Manager (Development)
Infocom Network Limited, New Delhi
phone: +91(11)6152172 (221) (L) ,9811255597 (M)
Visit http://www.trade-india.com ,
India's Leading B2B eMarketplace.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mallah@trade-india.com, vampyre5@yahoo.com
Subject: Re: Help with query involving aggregation and joining.
In-Reply-To: <200302241854.21761.mallah@trade-india.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox