Re: simple (?) join
От | Oliveiros C, |
---|---|
Тема | Re: simple (?) join |
Дата | |
Msg-id | 1EF7ADDE66AA4D4E8E7EB2021CAD6CBB@marktestcr.marktest.pt обсуждение исходный текст |
Ответ на | simple (?) join (Gary Stainburn <gary.stainburn@ringways.co.uk>) |
Ответы |
Re: simple (?) join
(justin <justin@emproshunts.com>)
|
Список | pgsql-sql |
Hello, Justin, Gary.
Justin, your (the second one) query is not much different from mine.
You previewed the possibility of having orders without any matching entry on orders_log with your left join, something that I haven't. Gary, will you have
records on your orders table that don't reference any record on your orders_log table? If so, Justin's query is the right one you should use.
You return the full record from orders and an additional column from orders_log, the ol_timestamp column. I understood that Gary wanted the full record from orders_log, not
just the timestamp column. That part is done by my subquery .
I think Gary could clarify what he wants exactly. Gary? :)
Also, Justin, your query design seems right to me, but maybe you should add this (the part in comment) to your subquery
SELECT MAX(ol_timestamp) /* as ol_timestamp */ , o_id FROM orders_log group by o_id
because the MAX(ol_timestamp) will receive the name max, not ol_timestamp, and probably
the parser will complain that column ol_timestamp does not exist.
Ain't I right?
Best,
Oliveiros
----- Original Message -----From: justinTo: David W NoonSent: Thursday, September 24, 2009 9:15 PMSubject: Re: [SQL] simple (?) join
David W Noon wrote:On Thu, 24 Sep 2009 16:16:36 +0100, Gary Stainburn wrote about [SQL] simple (?) join: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?SELECT * FROM orders WHERE o_id IN (SELECT o_id FROM orders_log WHERE ol_timestamp = (SELECT MAX(ol_timestamp) FROM orders_log)); No joins required.
I don't think that is what he is requesting. I read it he also wants the timestamp included in the result set
A nested query
Select
orders.*,
(SELECT MAX(ol_timestamp) FROM orders_log where orders_log.o_id = orders.oid)
From orders
Still another option is using a join
Select
orders.*, ol_timestamp
From orders
left join (SELECT MAX(ol_timestamp), o_id FROM orders_log group by o_id) as JoinQuery on JoinQuery.o_id = orders.o_id
The second one should be faster
В списке pgsql-sql по дате отправления: