Thread: a question on SQL
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? I'm perplexed by the simplicity of the task and the fact that what solutions I did come up with fail to perform very well (a subselect with ORDER BY MIN(production_date) LIMIT 1) or are rather complex (using temp tables). Hints? TIA, t.n.a.
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/ > From each according to his ability. To each according to his ability to litigate.
Attachment
-----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 offsetsthough (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 learnedon Informix so I actually thought it was standard, until reality disabused me of the notion. Greg Williamson DBA GlobeXplorer LLC