a question on SQL

On �ri, 2006-12-12 at 16:47 +0000, Tomi N/A wrote:

Don't really know where to ask this...the general mailing list sounds
like the closest.

Let's say I have three tables: owner, factory and product with a 1:N
relationship at each step.
Assuming that a product has a production date, how would you go about
returning a factory for every owner, where the returned factory is the
factory that produced the oldest product of it's owner?

Hints?

someting like this maybe:
select distinct on (owner.id,factory.factoryid) *
from owner,factory,product
where <your join contitions>
order by owner.id,factory.factoryid,production_date

gnari

Seems that a recursive use of "DISTINCT ON" will do it:

create table factories (id int, factory varchar(10), ownerid int);
create table products (id int, product varchar(10), atime int
,factory_id int);

--owner 1 : factory 1
insert into products values(1,'p1',123,1);
insert into products values(2,'p2',124,1);
insert into products values(3,'p3',125,1);

--owner 1 : factory 2
insert into products values(4,'p4',1,2);-- expected

--owner 2 : factory 3
insert into products values(5,'p5',127,3);-- expected
insert into products values(6,'p6',128,3);
insert into products values(7,'p7',129,3);

insert into factories values(1,'f1',1);
insert into factories values(2,'f2',1);
insert into factories values(3,'f3',2);

select distinct on (foo.ownerid)
foo.ownerid,foo.factory,foo.atime
from
(select distinct on (f.ownerid, p.factory_id)
f.ownerid,factory,atime
from factories f,products p
where p.factory_id=f.id
order by f.ownerid, p.factory_id, atime
)foo
order by foo.ownerid, foo.atime

Cheers,

Marc

Ragnar, Marc, thanks so much for the help: DISTINCT ON was *exactly*
what I needed.
It's not a part of any SQL standard I know of, but does the job _wonderfully_.

Cheers,
t.n.a.

On Tue, Dec 12, 2006 at 06:29:07PM +0000, Tomi N/A wrote:

Ragnar, Marc, thanks so much for the help: DISTINCT ON was *exactly*
what I needed.
It's not a part of any SQL standard I know of, but does the job
_wonderfully_.

It's the single most useful non-standard SQL feature postgresql has. It
is thus simultaneously bad (from a portatbility aspect) and brilliant
(because it's a million times easier and faster than the alternatives).

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Ragnar, Marc, thanks so much for the help: DISTINCT ON was *exactly*
what I needed.
It's not a part of any SQL standard I know of, but does the job
_wonderfully_.

It's the single most useful non-standard SQL feature postgresql has. It
is thus simultaneously bad (from a portatbility aspect) and brilliant
(because it's a million times easier and faster than the alternatives).

You mean second-most useful. LIMIT/OFFSET is the champion, hand down. :)

- --
Greg Sabino Mullane greg@turnstep.com
End Point http://www.endpoint.com/
PGP Key: 0x14964AC8 200612121616
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8

-----BEGIN PGP SIGNATURE-----

iD8DBQFFfxxjvJuQZxSWSsgRAokYAKDbmzEdfi3B/Fp0L62C6Fn48saMigCfeANo
PFT+tLmygoaZpAqfDO241AQ=
=n0xI
-----END PGP SIGNATURE-----

"Greg Sabino Mullane" <greg@turnstep.com> writes:

It's the single most useful non-standard SQL feature postgresql has. It
is thus simultaneously bad (from a portatbility aspect) and brilliant
(because it's a million times easier and faster than the alternatives).

You mean second-most useful. LIMIT/OFFSET is the champion, hand down. :)

Yeah, but that one's only quasi-non-standard ... several other DBMSes
have it too.

regards, tom lane

Tom Lane wrote:

"Greg Sabino Mullane" <greg@turnstep.com> writes:

It's the single most useful non-standard SQL feature postgresql has. It
is thus simultaneously bad (from a portatbility aspect) and brilliant
(because it's a million times easier and faster than the alternatives).

You mean second-most useful. LIMIT/OFFSET is the champion, hand down. :)

Yeah, but that one's only quasi-non-standard ... several other DBMSes
have it too.

I know MySQL has it, and SQL Lite added it. Which other ones? Someone
asked me recently. I see this chart from Perl documentation:

http://search.cpan.org/~davebaird/SQL-Abstract-Limit-0.12/lib/SQL/Abstract/Limit.pm#DESCRIPTION

Oh, and Rasmus Lerdorf told me he invented LIMIT for mSQL, and MySQL
then added it, and that MySQL added the limit option.

This was interesting in the MySQL manuals:

For compatibility with PostgreSQL, MySQL also supports the LIMIT
row_count OFFSET offset syntax.

Did we add the OFFSET _keyword_. I remember we had the comma-ed numbers
backwards, and we had OFFSET, but I thought that keyword came from
MySQL. Obviously, they don't think so.

--
Bruce Momjian bruce@momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Bruce Momjian wrote:

Tom Lane wrote:

"Greg Sabino Mullane" <greg@turnstep.com> writes:

It's the single most useful non-standard SQL feature postgresql has. It
is thus simultaneously bad (from a portatbility aspect) and brilliant
(because it's a million times easier and faster than the alternatives).

You mean second-most useful. LIMIT/OFFSET is the champion, hand down. :)

Yeah, but that one's only quasi-non-standard ... several other DBMSes
have it too.

I know MySQL has it, and SQL Lite added it. Which other ones? Someone
asked me recently. I see this chart from Perl documentation:

http://search.cpan.org/~davebaird/SQL-Abstract-Limit-0.12/lib/SQL/Abstract/Limit.pm#DESCRIPTION

Oh, and Rasmus Lerdorf told me he invented LIMIT for mSQL, and MySQL
then added it, and that MySQL added the limit option.

This was interesting in the MySQL manuals:

For compatibility with PostgreSQL, MySQL also supports the LIMIT
row_count OFFSET offset syntax.

Did we add the OFFSET _keyword_. I remember we had the comma-ed numbers
backwards, and we had OFFSET, but I thought that keyword came from
MySQL. Obviously, they don't think so.

Informix provides the "FIRST" syntax to get the leading rows of a set; I think you have to use cursors to get further offsets though (been a while since I have had to use it), e.g. "SELECT FIRST 10 col1, col2, col3 FROM foo WHERE ...". No "LAST" either (just tried).

They have had this since at least IDS 8 and I thing the 7.x series had it as well. No idea where they got it from; I learned on Informix so I actually thought it was standard, until reality disabused me of the notion.

Greg Williamson
DBA
GlobeXplorer LLC