Re: [GENERAL] a question on SQL
Bruce Momjian wrote: > > Tom Lane wrote: > > "Greg Sabino Mullane" <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] a question on SQL
Tom Lane wrote: > "Greg Sabino Mullane" <[EMAIL PROTECTED]> 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 [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] a question on SQL
"Greg Sabino Mullane" <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] a question on SQL
-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 [EMAIL PROTECTED] 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- ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] a question on SQL
On Tue, Dec 12, 2006 at 06:29:07PM +, 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 http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] a question on SQL
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. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] a question on SQL
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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] a question on SQL
On þri, 2006-12-12 at 16:47 +, 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 order by owner.id,factory.factoryid,production_date gnari ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] 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. ---(end of broadcast)--- TIP 6: explain analyze is your friend
