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.crist...@marktest.pt>
To: "Gary Stainburn" <gary.stainb...@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.stainb...@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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
