pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

From: Bruno Wolff III <bruno@wolff.to>
To: Eddie Cheung <vampyre5@yahoo.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Help with query involving aggregation and joining.
Date: Sun, 23 Feb 2003 14:45:42 -0600
Message-ID: <20030223204542.GA6337@wolff.to> (raw)
In-Reply-To: <20030223190227.21867.qmail@web80401.mail.yahoo.com>
References: <20030223190227.21867.qmail@web80401.mail.yahoo.com>

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;

view thread (12+ messages)  latest in thread

Message-ID: <20030223204542.GA6337@wolff.to>
Permalink:  ../20030223204542.GA6337@wolff.to/
Also on:    postgresql.org/message-id/20030223204542.GA6337@wolff.to

copy link · copy postgr.es

reply

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: bruno@wolff.to, vampyre5@yahoo.com
  Subject: Re: Help with query involving aggregation and joining.
  In-Reply-To: <20030223204542.GA6337@wolff.to>

* 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