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

help / color / mirror / Atom feed

From: Oliveiros C, <oliveiros.cristina@marktest.pt>
To: Oliveiros C, <oliveiros.cristina@marktest.pt>
To: Gary Stainburn <gary.stainburn@ringways.co.uk>
To: pgsql-sql@postgresql.org
Subject: Re: simple (?) join
Date: Thu, 24 Sep 2009 18:26:35 +0100
Message-ID: <142D1FF8CDA64A6DAB32AF515A29ED24@marktestcr.marktest.pt> (raw)
References: <200909241616.36933.gary.stainburn@ringways.co.uk>
	<2E407078A6154CF6AFFC6FEFBCF7BFF0@marktestcr.marktest.pt>

Hmm...no, it seems, it is not allowable to
use orders.* on a
GROUP BY clause.

Unless you've defined for the table something called an ordering operator.

If you didn't, you'll have to include all the fields from the orders table 
in the GROUP BY clause

HTH

Best,
Oliveiros

----- Original Message ----- 
From: "Oliveiros C," <oliveiros.cristina@marktest.pt>
To: "Gary Stainburn" <gary.stainburn@ringways.co.uk>; 
<pgsql-sql@postgresql.org>
Sent: Thursday, September 24, 2009 6:17 PM
Subject: Re: [SQL] simple (?) join


> You mean
> to list the complete orders table and for each of its records, the 
> corresponding record on the orders_log with the latest ol_timestamp?
>
>
> SELECT *
> FROM orders_log main
> JOIN
> (
> SELECT orders.*,  MAX(orders_log.ol_timestamp) as latest
> FROM orders
> NATURAL JOIN orders_log
> GROUP BY orders.*
> ) subquery
> ON main.ol_timestamp = subquery.latest
> AND main.o_id = subquery.o_id
>
> This query is untested, but could you give it a try?
>
> Then tell me the results.
>
> NB - I am not sure if it is legal to use * on a GROUP BY clause, but if it 
> isnt please kindly substitute by orders.o_id, orders.next_field, etc...
>
> Best,
> Oliveiros
>
>
> ----- Original Message ----- 
> From: "Gary Stainburn" <gary.stainburn@ringways.co.uk>
> To: <pgsql-sql@postgresql.org>
> Sent: Thursday, September 24, 2009 4:16 PM
> Subject: [SQL] simple (?) join
>
>
>> Hi folks.
>>
>> I have two tables
>>
>> create table orders (
>> o_id serial primary key
>> ...
>> );
>>
>> create table orders_log (
>> ol_id serial primary key,
>> o_id int4 not null references orders(o_id),
>> ol_timestamp timestamp,
>> ol_user,
>> );
>>
>> How can I select all from orders and the last (latest) entry from the
>> orders_log?
>>
>> Cheers
>> -- 
>> Gary Stainburn
>>
>> Gary's Haircut 700
>> Please visit http://www.justgiving.com/Gary-Stainburn/ to help me
>> raise money for Cancer Research - in return I'll have my head shaved
>>
>>
>> -- 
>> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-sql
>
>
> -- 
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql 

view thread (25+ messages)  latest in thread

Message-ID: <142D1FF8CDA64A6DAB32AF515A29ED24@marktestcr.marktest.pt>
Permalink:  ../142D1FF8CDA64A6DAB32AF515A29ED24@marktestcr.marktest.pt/
Also on:    postgresql.org/message-id/142D1FF8CDA64A6DAB32AF515A29ED24@marktestcr.marktest.pt

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: oliveiros.cristina@marktest.pt, gary.stainburn@ringways.co.uk
  Subject: Re: simple (?) join
  In-Reply-To: <142D1FF8CDA64A6DAB32AF515A29ED24@marktestcr.marktest.pt>

* 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