pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedsimple? join
25+ messages / 10 participants
[nested] [flat]
* simple? join
@ 2002-01-04 17:10 Frank Bax <fbax@sympatico.ca>
0 siblings, 2 replies; 25+ messages in thread
From: Frank Bax @ 2002-01-04 17:10 UTC (permalink / raw)
To: pgsql-sql
EMPLOYEE table contains: emp, first, last (emp is unique key)
TIMESHEET table contains: emp, timestamp, hours
I want to report employee name and total hours.
So far, I've come up with:
SELECT min(emp.first) as first, min(emp.last) as last, sum(ts.hours)
FROM timesheet ts, employee emp
WHERE ts.emp = emp.emp
GROUP by emp.emp
ORDER BY last, first;
It seems silly to specify "min" when I know there is only one value per emp!
SELECT first, last, sum(ts.hours)
FROM timesheet ts, employee emp
WHERE ts.emp = emp.emp
GROUP by emp.emp, first, last
ORDER BY last, first;
It seems silly to specify extraneous "group by" fields.
Is there a 'better' way to write this SQL?
Frank
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: simple? join
@ 2002-01-06 05:22 Peter Eisentraut <peter_e@gmx.net>
parent: Frank Bax <fbax@sympatico.ca>
1 sibling, 0 replies; 25+ messages in thread
From: Peter Eisentraut @ 2002-01-06 05:22 UTC (permalink / raw)
To: Frank Bax <fbax@sympatico.ca>; +Cc: pgsql-sql
Frank Bax writes:
> EMPLOYEE table contains: emp, first, last (emp is unique key)
> TIMESHEET table contains: emp, timestamp, hours
>
> I want to report employee name and total hours.
>
> So far, I've come up with:
>
> SELECT min(emp.first) as first, min(emp.last) as last, sum(ts.hours)
> FROM timesheet ts, employee emp
> WHERE ts.emp = emp.emp
> GROUP by emp.emp
> ORDER BY last, first;
>
> It seems silly to specify "min" when I know there is only one value per emp!
Indeed this looks strange.
> SELECT first, last, sum(ts.hours)
> FROM timesheet ts, employee emp
> WHERE ts.emp = emp.emp
> GROUP by emp.emp, first, last
> ORDER BY last, first;
>
> It seems silly to specify extraneous "group by" fields.
There's nothing "extraneous" there. Both first and last could be
duplicated, so you need to group by each one.
> Is there a 'better' way to write this SQL?
Not really. The latter is how I would write it.
--
Peter Eisentraut peter_e@gmx.net
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: simple? join
@ 2002-01-07 22:08 Frank Bax <fbax@sympatico.ca>
parent: Frank Bax <fbax@sympatico.ca>
1 sibling, 3 replies; 25+ messages in thread
From: Frank Bax @ 2002-01-07 22:08 UTC (permalink / raw)
To: pgsql-sql
At 12:22 AM 1/6/02 -0500, Peter Eisentraut wrote:
>Frank Bax writes:
>> EMPLOYEE table contains: emp, first, last (emp is unique key)
>> TIMESHEET table contains: emp, timestamp, hours
>> I want to report employee name and total hours.
>> SELECT first, last, sum(ts.hours)
>> FROM timesheet ts, employee emp
>> WHERE ts.emp = emp.emp
>> GROUP by emp.emp, first, last
>> ORDER BY last, first;
>>
>> It seems silly to specify extraneous "group by" fields.
>
>There's nothing "extraneous" there. Both first and last could be
>duplicated, so you need to group by each one.
But first and last can't be duplicated if emp is defined as unique. If I
am also selecting a dozen or so other fields from "employee" table, must I
also include them all in the GROUP BY clause, even though I know "emp"
identifies a unique row in this table?
Frank
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: simple? join
@ 2002-01-07 22:52 Peter Eisentraut <peter_e@gmx.net>
parent: Frank Bax <fbax@sympatico.ca>
2 siblings, 0 replies; 25+ messages in thread
From: Peter Eisentraut @ 2002-01-07 22:52 UTC (permalink / raw)
To: Frank Bax <fbax@sympatico.ca>; +Cc: pgsql-sql
Frank Bax writes:
> At 12:22 AM 1/6/02 -0500, Peter Eisentraut wrote:
> >Frank Bax writes:
> >> EMPLOYEE table contains: emp, first, last (emp is unique key)
> >> TIMESHEET table contains: emp, timestamp, hours
> >> I want to report employee name and total hours.
> >> SELECT first, last, sum(ts.hours)
> >> FROM timesheet ts, employee emp
> >> WHERE ts.emp = emp.emp
> >> GROUP by emp.emp, first, last
> >> ORDER BY last, first;
> >>
> >> It seems silly to specify extraneous "group by" fields.
> >
> >There's nothing "extraneous" there. Both first and last could be
> >duplicated, so you need to group by each one.
>
> But first and last can't be duplicated if emp is defined as unique.
Strictly speaking, you're right. However, by the time you get to GROUP BY
the tables have been joined so the notion of a unique constraint has been
lost. Maybe it shouldn't, but as it stands, there's nothing you can do
better here.
> If I
> am also selecting a dozen or so other fields from "employee" table, must I
> also include them all in the GROUP BY clause, even though I know "emp"
> identifies a unique row in this table?
Yes you do. There's the possibility to write it differently like so:
SELECT *
FROM
(SELECT emp, sum(ts.hours)
FROM timesheet ts, employee emp
WHERE ts.emp = emp.emp
GROUP by emp.emp) AS a
INNER JOIN
(SELECT emp, first, last, more, things, here FROM employee) AS b
ON (a.emp = b.emp)
...
This could be useful if the second query in the inner join involves more
than one table, but on the whole this can get pretty messy.
--
Peter Eisentraut peter_e@gmx.net
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: simple? join
@ 2002-01-07 23:25 Tom Lane <tgl@sss.pgh.pa.us>
parent: Frank Bax <fbax@sympatico.ca>
2 siblings, 1 reply; 25+ messages in thread
From: Tom Lane @ 2002-01-07 23:25 UTC (permalink / raw)
To: Frank Bax <fbax@sympatico.ca>; +Cc: pgsql-sql
Frank Bax <fbax@sympatico.ca> writes:
> But first and last can't be duplicated if emp is defined as unique. If I
> am also selecting a dozen or so other fields from "employee" table, must I
> also include them all in the GROUP BY clause, even though I know "emp"
> identifies a unique row in this table?
SQL92 says you must.
SQL99, however, seems to understand the concept you are putting forward.
If I understand what they're getting at, the notion of "functional
dependency" that is defined at excruciating length in SQL99 is
essentially that a unique key functionally determines all the other
columns in its table. And the places where SQL92 says something like
"shall reference a grouping column of T or shall be specified within a
<set function specification>" are changed in SQL99 to read "shall
reference a column that is functionally dependent on G or shall be
specified within a <set function specification>" (G being the set of
grouping columns).
Also, SQL99 considers this an optional feature:
3) Without Feature T301, "Functional dependencies", if T is
a grouped table, then in each <value expression>, each
<column reference> that references a column of T shall
reference a grouping column or be specified in a <set function
specification>.
So, when and if we get around to implementing this particular SQL99
feature, what you are suggesting will work. Right now it doesn't
(and I'll wager that darn few other SQL implementations support this
feature as yet, either).
BTW, SQL99's GROUP BY clause seems to have a whole bunch of baroque
new features besides this one.
regards, tom lane
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: simple? join
@ 2002-01-08 01:54 Christopher Kings-Lynne <chriskl@familyhealth.com.au>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 25+ messages in thread
From: Christopher Kings-Lynne @ 2002-01-08 01:54 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; Frank Bax <fbax@sympatico.ca>; +Cc: pgsql-sql
> So, when and if we get around to implementing this particular SQL99
> feature, what you are suggesting will work. Right now it doesn't
> (and I'll wager that darn few other SQL implementations support this
> feature as yet, either).
I think MySQL does. I always found it strange that I had to add columns to
the group by clause in postgres that were functionally dependent on the
"real" grouped variable.
In fact, before I realised that you had to add them to the group by clause,
I just wrapped the functionally dependent fields in a MAX function (or min,
whatever).
ie. SELECT orders.product_id, MAX(product_name), MAX(unit_price) FROM
products NATURAL JOIN orders GROUP BY orders.product_id;
In fact, if one created a 'do nothing' aggregate function, you could avoid
having to add things to the group by clause in a cleaner fashion.
Chris
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: simple? join
@ 2002-01-08 03:25 Tom Lane <tgl@sss.pgh.pa.us>
parent: Christopher Kings-Lynne <chriskl@familyhealth.com.au>
0 siblings, 0 replies; 25+ messages in thread
From: Tom Lane @ 2002-01-08 03:25 UTC (permalink / raw)
To: Christopher Kings-Lynne <chriskl@familyhealth.com.au>; +Cc: Frank Bax <fbax@sympatico.ca>; pgsql-sql
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
>> So, when and if we get around to implementing this particular SQL99
>> feature, what you are suggesting will work. Right now it doesn't
>> (and I'll wager that darn few other SQL implementations support this
>> feature as yet, either).
> I think MySQL does.
Do they bother to check validity of the column reference at all?
The cheesy way to do this would be not to check; if you referenced a
non-functionally-dependent column, you'd get a randomly chosen element
value with no warning... but for correct queries it would look like
they had support.
regards, tom lane
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: simple? join
@ 2002-01-08 19:03 Frank Bax <fbax@sympatico.ca>
parent: Frank Bax <fbax@sympatico.ca>
2 siblings, 0 replies; 25+ messages in thread
From: Frank Bax @ 2002-01-08 19:03 UTC (permalink / raw)
To: pgsql-sql
At 05:52 PM 1/7/02 -0500, Peter Eisentraut wrote:
>Frank Bax writes:
>
>> At 12:22 AM 1/6/02 -0500, Peter Eisentraut wrote:
>> >Frank Bax writes:
>> >> EMPLOYEE table contains: emp, first, last (emp is unique key)
>> >> TIMESHEET table contains: emp, timestamp, hours
>> >> I want to report employee name and total hours.
>> >> SELECT first, last, sum(ts.hours)
>> >> FROM timesheet ts, employee emp
>> >> WHERE ts.emp = emp.emp
>> >> GROUP by emp.emp, first, last
>> >> ORDER BY last, first;
>> >>
>> >> It seems silly to specify extraneous "group by" fields.
>> >
>> >There's nothing "extraneous" there. Both first and last could be
>> >duplicated, so you need to group by each one.
>>
>> But first and last can't be duplicated if emp is defined as unique.
>
>Strictly speaking, you're right. However, by the time you get to GROUP BY
>the tables have been joined so the notion of a unique constraint has been
>lost. Maybe it shouldn't, but as it stands, there's nothing you can do
>better here.
>
>> If I
>> am also selecting a dozen or so other fields from "employee" table, must I
>> also include them all in the GROUP BY clause, even though I know "emp"
>> identifies a unique row in this table?
>
>Yes you do. There's the possibility to write it differently like so:
>
>SELECT *
>FROM
> (SELECT emp, sum(ts.hours)
> FROM timesheet ts, employee emp
> WHERE ts.emp = emp.emp
> GROUP by emp.emp) AS a
> INNER JOIN
> (SELECT emp, first, last, more, things, here FROM employee) AS b
> ON (a.emp = b.emp)
>...
>
>This could be useful if the second query in the inner join involves more
>than one table, but on the whole this can get pretty messy.
Thanks for the sample!! A few more changes, and it's quite readable:
SELECT * FROM
(SELECT emp, sum(hours) FROM timesheet GROUP by emp) AS ts
INNER JOIN
(SELECT emp, first, last FROM employee) AS emp
ON (ts.emp = emp.emp)
As an added bonus, this runs almost twice as fast as either of the
originals (both original versions used same plan)!
Frank
^ permalink raw reply [nested|flat] 25+ messages in thread
* simple (?) join
@ 2009-09-24 15:16 Gary Stainburn <gary.stainburn@ringways.co.uk>
0 siblings, 4 replies; 25+ messages in thread
From: Gary Stainburn @ 2009-09-24 15:16 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: simple (?) join
@ 2009-09-24 17:17 Oliveiros C, <oliveiros.cristina@marktest.pt>
parent: Gary Stainburn <gary.stainburn@ringways.co.uk>
3 siblings, 1 reply; 25+ messages in thread
From: Oliveiros C, @ 2009-09-24 17:17 UTC (permalink / raw)
To: Gary Stainburn <gary.stainburn@ringways.co.uk>; pgsql-sql
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
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: simple (?) join
@ 2009-09-24 17:26 Oliveiros C, <oliveiros.cristina@marktest.pt>
parent: Oliveiros C, <oliveiros.cristina@marktest.pt>
0 siblings, 0 replies; 25+ messages in thread
From: Oliveiros C, @ 2009-09-24 17:26 UTC (permalink / raw)
To: Oliveiros C, <oliveiros.cristina@marktest.pt>; Gary Stainburn <gary.stainburn@ringways.co.uk>; pgsql-sql
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
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: simple (?) join
@ 2009-09-24 18:46 David W Noon <dwnoon@ntlworld.com>
parent: Gary Stainburn <gary.stainburn@ringways.co.uk>
3 siblings, 1 reply; 25+ messages in thread
From: David W Noon @ 2009-09-24 18:46 UTC (permalink / raw)
To: pgsql-sql
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.
--
Regards,
Dave [RLU #314465]
=======================================================================
david.w.noon@ntlworld.com (David W Noon)
=======================================================================
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: simple (?) join
@ 2009-09-24 20:15 justin <justin@emproshunts.com>
parent: David W Noon <dwnoon@ntlworld.com>
0 siblings, 2 replies; 25+ messages in thread
From: justin @ 2009-09-24 20:15 UTC (permalink / raw)
To: David W Noon <dwnoon@ntlworld.com>; +Cc: pgsql-sql; gary.stainburn@ringways.co.uk
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<br>
<br>
David W Noon wrote:
<blockquote cite="mid:20090924194629.44bc236f@dwnoon.ntlworld.com"
type="cite">
<pre wrap="">On Thu, 24 Sep 2009 16:16:36 +0100, Gary Stainburn wrote about [SQL]
simple (?) join:
</pre>
<blockquote type="cite">
<pre wrap="">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?
</pre>
</blockquote>
<pre wrap=""><!---->
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.
</pre>
</blockquote>
<br>
I don't think that is what he is requesting. I read it he also wants
the timestamp included in the result set<br>
<br>
A nested query <br>
<br>
Select <br>
orders.*, <br>
(SELECT MAX(ol_timestamp) FROM orders_log where orders_log.o_id =
orders.oid) <br>
From orders<br>
<br>
Still another option is using a join <br>
<br>
Select <br>
orders.*, ol_timestamp<br>
From orders <br>
left join (SELECT MAX(ol_timestamp), o_id FROM orders_log group by
o_id) as JoinQuery on JoinQuery.o_id = orders.o_id <br>
<br>
The second one should be faster<br>
<br>
<br>
<br>
</body>
</html>
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: simple (?) join
@ 2009-09-24 21:21 David W Noon <dwnoon@ntlworld.com>
parent: justin <justin@emproshunts.com>
1 sibling, 1 reply; 25+ messages in thread
From: David W Noon @ 2009-09-24 21:21 UTC (permalink / raw)
To: pgsql-sql
On Thu, 24 Sep 2009 16:15:07 -0400, justin wrote about 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
That shouldn't be too much of a problem.
>A nested query
>
>Select
> orders.*,
> (SELECT MAX(ol_timestamp) FROM orders_log where orders_log.o_id =
> orders.oid)
From orders
That won't give the desired results. I don't think the SQL parser will
even accept it.
>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
That won't give the desired results either. If you change "left" to
"inner" you will be closer though.
Both of your queries will retrieve the entire orders table with a
timestamp of some sort from the orders_log table.
>The second one should be faster
Try this for an elegant approach:
WITH maxi AS (SELECT MAX(ol_timestamp) AS ts FROM orders_log)
SELECT o.*, maxi.ts
FROM orders AS o
WHERE o.o_id IN (SELECT o_id FROM orders_log
WHERE ol_timestamp = maxi.ts);
This caches the latest timestamp in a temporary table so that it can
both appear in the result set and be used to constrain the selection
from the orders_log table.
--
Regards,
Dave [RLU #314465]
=======================================================================
david.w.noon@ntlworld.com (David W Noon)
=======================================================================
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: simple (?) join
@ 2009-09-25 03:06 Raj Mathur <raju@linux-delhi.org>
parent: Gary Stainburn <gary.stainburn@ringways.co.uk>
3 siblings, 0 replies; 25+ messages in thread
From: Raj Mathur @ 2009-09-25 03:06 UTC (permalink / raw)
To: pgsql-sql
On Thursday 24 Sep 2009, Gary Stainburn wrote:
> 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?
Does this do what you want? You would have to do some tricks if you
also want orders that don't have any entry in the orders_log table to be
displayed.
select ol.ol_id,ol.o_id,ol.ol_timestamp
from orders o natural join orders_log ol
where (ol.o_id,ol.ol_timestamp)
in (select o_id,max(ol_timestamp) from orders_log group by o_id);
Regards,
-- Raju
--
Raj Mathur raju@kandalaya.org http://kandalaya.org/
GPG: 78D4 FC67 367F 40E2 0DD5 0FEF C968 D0EF CC68 D17F
PsyTrance & Chill: http://schizoid.in/ || It is the mind that moves
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: simple (?) join
@ 2009-09-25 11:00 Oliveiros C, <oliveiros.cristina@marktest.pt>
parent: justin <justin@emproshunts.com>
1 sibling, 1 reply; 25+ messages in thread
From: Oliveiros C, @ 2009-09-25 11:00 UTC (permalink / raw)
To: justin <justin@emproshunts.com>; +Cc: pgsql-sql; gary.stainburn@ringways.co.uk
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: justin
To: David W Noon
Cc: pgsql-sql@postgresql.org ; gary.stainburn@ringways.co.uk
Sent: Thursday, September 24, 2009 9:15 PM
Subject: 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
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: simple (?) join
@ 2009-09-25 22:36 Claus Guttesen <kometen@gmail.com>
parent: Gary Stainburn <gary.stainburn@ringways.co.uk>
3 siblings, 0 replies; 25+ messages in thread
From: Claus Guttesen @ 2009-09-25 22:36 UTC (permalink / raw)
To: Gary Stainburn <gary.stainburn@ringways.co.uk>; +Cc: pgsql-sql
> 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?
Maby this will work?
Get the unique o_id from the table orders and do a group by in a
subselect, and then select the other fields from the same table, ie:
select o_od, max(o_field1), max(o_field2) ... from orders where o_id
in (select o_id from orders group by o_id) group by o_id order by
o_id;
This off course assumes that o_id is a serial (and thus that timestamp
will always be higher etc.).
--
regards
Claus
When lenity and cruelty play for a kingdom,
the gentler gamester is the soonest winner.
Shakespeare
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: simple (?) join
@ 2009-09-26 18:53 justin <justin@emproshunts.com>
parent: Oliveiros C, <oliveiros.cristina@marktest.pt>
0 siblings, 0 replies; 25+ messages in thread
From: justin @ 2009-09-26 18:53 UTC (permalink / raw)
To: Oliveiros C, <oliveiros.cristina@marktest.pt>; +Cc: pgsql-sql; gary.stainburn@ringways.co.uk
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<br>
<br>
Oliveiros C, wrote:
<blockquote
cite="mid:1EF7ADDE66AA4D4E8E7EB2021CAD6CBB@marktestcr.marktest.pt"
type="cite">
<meta http-equiv="Content-Type" content="text/html; ">
<meta content="MSHTML 6.00.5730.13" name="GENERATOR">
<style></style>
<div><font face="Arial" size="2">Hello, Justin, Gary.</font></div>
<div> </div>
<div><font face="Arial" size="2">Justin, your (the second one) query
is not much different from mine.</font></div>
</blockquote>
No its not, <br>
<blockquote
cite="mid:1EF7ADDE66AA4D4E8E7EB2021CAD6CBB@marktestcr.marktest.pt"
type="cite">
<div><font face="Arial" size="2"> 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</font></div>
<div><font face="Arial" size="2">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.</font></div>
<div> </div>
<div><font face="Arial" size="2">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</font></div>
<div><font face="Arial" size="2">just the timestamp column. That
part is done by my subquery .</font></div>
<div> </div>
<div><font face="Arial" size="2"> I think Gary could clarify what he
wants exactly. Gary? :)</font></div>
</blockquote>
Yes gary please do if these do not answer your questions???<br>
<blockquote
cite="mid:1EF7ADDE66AA4D4E8E7EB2021CAD6CBB@marktestcr.marktest.pt"
type="cite">
<div> </div>
<div><font face="Arial" size="2">Also, Justin, your query
design seems right to me, but maybe you should add this (the part in
comment) to your subquery </font></div>
<div>SELECT MAX(ol_timestamp) /* as ol_timestamp */ , o_id FROM
orders_log group by o_id </div>
<div>because the MAX(ol_timestamp) will receive the name max, not
ol_timestamp, and probably</div>
<div>the parser will complain that column ol_timestamp does not
exist.</div>
<div>Ain't I right?</div>
</blockquote>
<br>
Yes you are correct i forgot to add the AS <br>
</body>
</html>
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: simple (?) join
@ 2009-09-26 18:54 justin <justin@emproshunts.com>
parent: David W Noon <dwnoon@ntlworld.com>
0 siblings, 1 reply; 25+ messages in thread
From: justin @ 2009-09-26 18:54 UTC (permalink / raw)
To: David W Noon <dwnoon@ntlworld.com>; +Cc: pgsql-sql
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
<br>
<br>
David W Noon wrote:
<blockquote cite="mid:20090924222106.23a5c1f3@dwnoon.ntlworld.com"
type="cite">
<blockquote type="cite">
<pre wrap="">A nested query
Select
orders.*,
(SELECT MAX(ol_timestamp) FROM orders_log where orders_log.o_id =
orders.oid)
</pre>
</blockquote>
<pre wrap=""><!---->>From orders
That won't give the desired results. I don't think the SQL parser will
even accept it.
</pre>
</blockquote>
Yes this is a valid SQL statement use similar ones a great deal. The
problem with nested queries is they can only return 1 record per
match. Another problem can be performance it has to run for every
record in order tables and it occurs pre filter conditions <br>
<blockquote cite="mid:20090924222106.23a5c1f3@dwnoon.ntlworld.com"
type="cite">
<pre wrap="">
</pre>
<blockquote type="cite">
<pre wrap="">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
</pre>
</blockquote>
<pre wrap=""><!---->
That won't give the desired results either. If you change "left" to
"inner" you will be closer though.
Both of your queries will retrieve the entire orders table with a
timestamp of some sort from the orders_log table.
</pre>
</blockquote>
<br>
I read the question as Gary wanted the entire orders table with the
greatest timestamp from the logs table....<br>
<br>
Quoting Gary<br>
<pre wrap="">"How can I select <u><big><big><b>all from orders</b></big></big></u> and the <b><u>last (latest)</u></b> entry from the
orders_log?"</pre>
<br>
<br>
<br>
<br>
</body>
</html>
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: simple (?) join
@ 2009-09-26 19:53 David W Noon <dwnoon@ntlworld.com>
parent: justin <justin@emproshunts.com>
0 siblings, 1 reply; 25+ messages in thread
From: David W Noon @ 2009-09-26 19:53 UTC (permalink / raw)
To: pgsql-sql
On Sat, 26 Sep 2009 14:54:24 -0400, justin wrote about Re: [SQL] simple
(?) join:
[snip]
>Quoting Gary
>"How can I select all from orders and the last (latest) entry from the
>orders_log?"
In that case, a simple Cartesian product will do:
SELECT o.*, maxi.ts
FROM orders AS o,
(SELECT MAX(ol_timestamp) AS ts FROM orders_log) AS maxi;
Since the cardinality of the subquery "maxi" is 1, it will give a result
set with cardinality of the complete orders table.
I don't understand why anybody would want to do that. [De gustibus ... ]
--
Regards,
Dave [RLU #314465]
=======================================================================
david.w.noon@ntlworld.com (David W Noon)
=======================================================================
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: simple (?) join
@ 2009-09-26 20:15 justin <justin@emproshunts.com>
parent: David W Noon <dwnoon@ntlworld.com>
0 siblings, 1 reply; 25+ messages in thread
From: justin @ 2009-09-26 20:15 UTC (permalink / raw)
To: David W Noon <dwnoon@ntlworld.com>; pgsql-sql
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
David W Noon wrote:
<blockquote cite="mid:20090926205349.5fa284f1@dwnoon.ntlworld.com"
type="cite">
<pre wrap="">On Sat, 26 Sep 2009 14:54:24 -0400, justin wrote about Re: [SQL] simple
(?) join:
[snip]
</pre>
<blockquote type="cite">
<pre wrap="">Quoting Gary
"How can I select all from orders and the last (latest) entry from the
orders_log?"
</pre>
</blockquote>
<pre wrap=""><!---->
In that case, a simple Cartesian product will do:
SELECT o.*, maxi.ts
FROM orders AS o,
(SELECT MAX(ol_timestamp) AS ts FROM orders_log) AS maxi;
Since the cardinality of the subquery "maxi" is 1, it will give a result
set with cardinality of the complete orders table.
I don't understand why anybody would want to do that. [De gustibus ... ]
</pre>
</blockquote>
<br>
Guessing here <br>
<br>
Answer to return the last time someone either viewed or edited the
order.<br>
<br>
This is a very common audit requirement to track who what, when and why
something happened. <br>
</body>
</html>
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: simple (?) join
@ 2009-09-28 11:02 Gary Stainburn <gary.stainburn@ringways.co.uk>
parent: justin <justin@emproshunts.com>
0 siblings, 3 replies; 25+ messages in thread
From: Gary Stainburn @ 2009-09-28 11:02 UTC (permalink / raw)
To: pgsql-sql
On Saturday 26 September 2009 21:15:37 justin wrote:
> David W Noon wrote:
> On Sat, 26 Sep 2009 14:54:24 -0400, justin wrote about Re: [SQL] simple
> (?) join:
>
> [snip]
>
> Quoting Gary
> "How can I select all from orders and the last (latest) entry from the
> orders_log?"
>
>
> In that case, a simple Cartesian product will do:
>
> SELECT o.*, maxi.ts
> FROM orders AS o,
> (SELECT MAX(ol_timestamp) AS ts FROM orders_log) AS maxi;
>
> Since the cardinality of the subquery "maxi" is 1, it will give a result
> set with cardinality of the complete orders table.
>
> I don't understand why anybody would want to do that. [De gustibus ... ]
>
>
> Guessing here
>
> Answer to return the last time someone either viewed or edited the order.
>
> This is a very common audit requirement to track who what, when and why
> something happened.
For some reason the reply I sent on Friday didn't get through.
What I need is all of the order record and all of the latest log entry
returning as a join. Specifically I want for each order the most recent log
entry timestamp and it's associated user - i.e. who made the the last log
entry and when.
I suppose I'm asking how I would do the sub-query to pull the most recent log
entry per order.
--
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
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: simple (?) join
@ 2009-09-28 11:14 Oliveiros C, <oliveiros.cristina@marktest.pt>
parent: Gary Stainburn <gary.stainburn@ringways.co.uk>
2 siblings, 0 replies; 25+ messages in thread
From: Oliveiros C, @ 2009-09-28 11:14 UTC (permalink / raw)
To: Gary Stainburn <gary.stainburn@ringways.co.uk>; pgsql-sql
Hello, Gary.
thank you for your e-mail
This is a slightly modified version of the query I sent you on first place
(have you tried it out?).
It will return (I hope :) the full orders record plus the maximum
ol_timestamp and respective o_user.
HTH
Best,
Oliveiros
SELECT subquery.*, orders_log.ol_user
FROM orders_log main
JOIN
(
SELECT orders.o_id, /* The remaining fields of orders go here */,
MAX(orders_log.ol_timestamp) as latest
FROM orders
NATURAL JOIN orders_log
GROUP BY orders.o_id, /* the remaining fields of orders go here */
) subquery
ON main.ol_timestamp = subquery.latest
AND main.o_id = subquery.o_id
----- Original Message -----
From: "Gary Stainburn" <gary.stainburn@ringways.co.uk>
To: <pgsql-sql@postgresql.org>
Sent: Monday, September 28, 2009 12:02 PM
Subject: Re: [SQL] simple (?) join
On Saturday 26 September 2009 21:15:37 justin wrote:
> David W Noon wrote:
> On Sat, 26 Sep 2009 14:54:24 -0400, justin wrote about Re: [SQL] simple
> (?) join:
>
> [snip]
>
> Quoting Gary
> "How can I select all from orders and the last (latest) entry from the
> orders_log?"
>
>
> In that case, a simple Cartesian product will do:
>
> SELECT o.*, maxi.ts
> FROM orders AS o,
> (SELECT MAX(ol_timestamp) AS ts FROM orders_log) AS maxi;
>
> Since the cardinality of the subquery "maxi" is 1, it will give a result
> set with cardinality of the complete orders table.
>
> I don't understand why anybody would want to do that. [De gustibus ... ]
>
>
> Guessing here
>
> Answer to return the last time someone either viewed or edited the order.
>
> This is a very common audit requirement to track who what, when and why
> something happened.
For some reason the reply I sent on Friday didn't get through.
What I need is all of the order record and all of the latest log entry
returning as a join. Specifically I want for each order the most recent log
entry timestamp and it's associated user - i.e. who made the the last log
entry and when.
I suppose I'm asking how I would do the sub-query to pull the most recent
log
entry per order.
--
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
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: simple (?) join
@ 2009-09-28 11:27 David W Noon <dwnoon@ntlworld.com>
parent: Gary Stainburn <gary.stainburn@ringways.co.uk>
2 siblings, 0 replies; 25+ messages in thread
From: David W Noon @ 2009-09-28 11:27 UTC (permalink / raw)
To: pgsql-sql
On Mon, 28 Sep 2009 12:02:20 +0100, Gary Stainburn wrote about Re:
[SQL] simple (?) join:
[snip]
>For some reason the reply I sent on Friday didn't get through.
>What I need is all of the order record and all of the latest log entry
>returning as a join. Specifically I want for each order the most
>recent log entry timestamp and it's associated user - i.e. who made
>the the last log entry and when.
In that case, try this:
SELECT o.*, ol.ol_timestamp, ol.ol_user
FROM orders AS o
INNER JOIN orders_log AS ol
ON ol.o_id = o.o_id
WHERE ol.ol_timestamp = (SELECT MAX(ol_timestamp) FROM orders_log AS
ol2 WHERE ol2.o_id = o.o_id);
This will omit orders that have never been logged. You have not
specified what you want done with them.
--
Regards,
Dave [RLU #314465]
=======================================================================
david.w.noon@ntlworld.com (David W Noon)
=======================================================================
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: simple (?) join
@ 2009-09-28 13:43 Gary Stainburn <gary.stainburn@ringways.co.uk>
parent: Gary Stainburn <gary.stainburn@ringways.co.uk>
2 siblings, 0 replies; 25+ messages in thread
From: Gary Stainburn @ 2009-09-28 13:43 UTC (permalink / raw)
To: Jim <shakahshakah@gmail.com>; +Cc: pgsql-sql
On Monday 28 September 2009 13:30:32 you wrote:
> SELECT o.*, ol.*
> FROM orders o
> LEFT JOIN (
> SELECT ol.o_id, MAX(ol_timestamp) AS maxts
> FROM orders_log ol
> GROUP BY 1
> ) max_olt
> ON max_olt.o_id=o.o_id
> LEFT JOIN orders_log ol
> ON ol.o_id=o.o_id
> AND ol.ol_timestamp=max_olt.maxts
> ;
Cheers Jim,
That worked.
Gary
--
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
^ permalink raw reply [nested|flat] 25+ messages in thread
end of thread, other threads:[~2009-09-28 13:43 UTC | newest]
Thread overview: 25+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-01-04 17:10 simple? join Frank Bax <fbax@sympatico.ca>
2002-01-06 05:22 ` Peter Eisentraut <peter_e@gmx.net>
2002-01-07 22:08 ` Frank Bax <fbax@sympatico.ca>
2002-01-07 22:52 ` Peter Eisentraut <peter_e@gmx.net>
2002-01-07 23:25 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-01-08 01:54 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2002-01-08 03:25 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-01-08 19:03 ` Frank Bax <fbax@sympatico.ca>
2009-09-24 15:16 simple (?) join Gary Stainburn <gary.stainburn@ringways.co.uk>
2009-09-24 17:17 ` Re: simple (?) join Oliveiros C, <oliveiros.cristina@marktest.pt>
2009-09-24 17:26 ` Re: simple (?) join Oliveiros C, <oliveiros.cristina@marktest.pt>
2009-09-24 18:46 ` Re: simple (?) join David W Noon <dwnoon@ntlworld.com>
2009-09-24 20:15 ` Re: simple (?) join justin <justin@emproshunts.com>
2009-09-24 21:21 ` Re: simple (?) join David W Noon <dwnoon@ntlworld.com>
2009-09-26 18:54 ` Re: simple (?) join justin <justin@emproshunts.com>
2009-09-26 19:53 ` Re: simple (?) join David W Noon <dwnoon@ntlworld.com>
2009-09-26 20:15 ` Re: simple (?) join justin <justin@emproshunts.com>
2009-09-28 11:02 ` Re: simple (?) join Gary Stainburn <gary.stainburn@ringways.co.uk>
2009-09-28 11:14 ` Re: simple (?) join Oliveiros C, <oliveiros.cristina@marktest.pt>
2009-09-28 11:27 ` Re: simple (?) join David W Noon <dwnoon@ntlworld.com>
2009-09-28 13:43 ` Re: simple (?) join Gary Stainburn <gary.stainburn@ringways.co.uk>
2009-09-25 11:00 ` Re: simple (?) join Oliveiros C, <oliveiros.cristina@marktest.pt>
2009-09-26 18:53 ` Re: simple (?) join justin <justin@emproshunts.com>
2009-09-25 03:06 ` Re: simple (?) join Raj Mathur <raju@linux-delhi.org>
2009-09-25 22:36 ` Re: simple (?) join Claus Guttesen <kometen@gmail.com>
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