Re: [SQL] DIfference between max() and greatest() ?
max(expression) is an aggregate function (from the manual: "maximum value of expression across all input values") greatest(value [, ...]) is not an aggregate function (from the manual: "The GREATEST function selects the largest value from a list of any number of expressions.") So max takes the maximum of values coming from several rows, while greatest takes the maximum of values coming from 1 row. Assuming select model,count(distinct cars) from rc_cars group by model returns more than one record you will need both functions. Greatest gets the most recent date out of "bought" and "returned" PER RECORD. Max gets the most recent date out of all these greatest dates OVER ALL RECORDS. ModelBoughtReturned X2004-08-252005-01-01->Greatest = 2005-01-01 X2006-02-172006-02-18->Greatest = 2006-02-18 X2005-11-132001-05-16->Greatest = 2005-11-13 ¯ Max = 2006-02-18 That being said, isn't it unlikely that "bought" is more recent than "returned"? I can imagine that one can only return a car after buying it. If so, writing ... WHERE max(returned) < current_date - interval '1 day' ... would be enough. Another thought: with this WHERE clause a car returned yesterday will not show up. Is that what you want? If not, use ... WHERE max(returned) < current_date ... Good luck >>> "Ruben Gouveia" [EMAIL PROTECTED]> 2008-09-11 19:33 >> ( mailto:[EMAIL PROTECTED]> ) What is the difference between these two. I know that max() is an aggregate function select model,count(distinct cars) from rc_cars where greatest(baught,returned) < current_date - integer '1' group by model; Do i need to have a max () around a greatest() to make sure i get the most recent of the two. select model,count(distinct cars) from rc_cars where max(greatest(baught,returned)) < current_date - integer '1' group by model;
Re: [SQL] DIfference between max() and greatest() ?
Ruben Gouveia wrote: What is the difference between these two. I know that max() is an aggregate function ... and greatest() is a normal single-row function. One works vertically, one works horizontally, if that helps you. :-) Or max() is like sum() and greatest is like +. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Pls Hlp: SQL Problem
Hengky Lie wrote: > Dear friends, Hello. Quick tip - don't reply to another message when starting a new thread, people will assume it's part of the old thread. [snip most of query] > / 100::numeric * > Food";3960.;*4200.*;*240.* > Why the calculation result has so many decimals ? Because you've cast 100 to numeric, rather than numeric(10,2) or whatever. This means you get the full accuracy on the calculation - it won't ever trim a numeric (that's the whole reason to have the type). -- Richard Huxton Archonet Ltd -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Pls Hlp: SQL Problem
Dear friends, I have problem in postgresql view. This is the view (Pay attention to the red and blue colours): SELECT tblpenjualan.tanggal, tblpenjualan."operator", tblpenjualan.noinvoice, tblpenjualan.bayar, tblpenjualan.bayarvocher, tblpenjualan.jam, tblpenjualan.id, tblpenjualan.jeniscard, tbltransaksi.kodeproduk, tbltransaksi.keluar, tbltransaksi.harga, tbltransaksi.disc1, tblproduk.namabarang, tblproduk.subkat, tblsubkategori.subkategori, tblkategoriproduk.kode, tblkategoriproduk.kategori, tbltransaksi.hargapokok * tbltransaksi.keluar AS modal, tbltransaksi.keluar * tbltransaksi.harga - tbltransaksi.disc1 / 100::numeric * (tbltransaksi.keluar * tbltransaksi.harga) AS jumlah, tbltransaksi.keluar * tbltransaksi.harga - tbltransaksi.disc1 / 100::numeric * (tbltransaksi.keluar * tbltransaksi.harga) - tbltransaksi.hargapokok * tbltransaksi.keluar AS profit FROM tblpenjualan JOIN tbltransaksi ON tblpenjualan.id = tbltransaksi.jualid LEFT JOIN tblproduk ON tbltransaksi.kodeproduk::text = tblproduk.kode::text LEFT JOIN tblsubkategori ON tblproduk.subkat::text = tblsubkategori.id::text LEFT JOIN tblkategoriproduk ON tblsubkategori.kategoriid::text = tblkategoriproduk.kode::text; The result is : "2008-07-20";"ULY";"01-200708";"Tunai";0.00;"1899-12-30 21:14:47+06:55:25";1;"";"8993417200021";1.00;4200.00;0.00;"ELIPS HAIR VITAMIN ";"121";"VITAMIN RAMBUT";"NF";"Non Food";3960.;4200.;240. "2008-07-20";"ULY";"01-200708";"Tunai";0.00;"1899-12-30 21:14:47+06:55:25";1;"";"899100425";3.00;1650.00;0.00;"TAKE-IT MILK CHOCOLATE WAFER 17 G";"8";"BISKUIT";"MKN";"Makanan";4680.;4950.;270. "2008-07-20";"ULY";"01-200708";"Tunai";0.00;"1899-12-30 21:14:47+06:55:25";1;"";"8991115000103";2.00;1600.00;0.00;"ALPENLIEBE ORIGINAL 41 G ISI 10 BTR";"62";"PERMEN";"MKN";"Makanan";3000.;3200.;200. "2008-07-20";"ULY";"01-200708";"Tunai";0.00;"1899-12-30 21:14:47+06:55:25";1;"";"8990800010533";1.00;1600.00;0.00;"MENTOS CHEWY DRAGEES 37 G";"62";"PERMEN";"MKN";"Makanan";1500.;1600.;100. Why the calculation result has so many decimals ? Thank you
Re: [SQL] DIfference between max() and greatest() ?
Thanks Bart, Your explanation is great. Returned and Bought are of datatypes date, hence Returned would be a more recent value. On Fri, Sep 12, 2008 at 12:10 AM, Bart Degryse <[EMAIL PROTECTED]>wrote: > max(*expression*) is an aggregate function > (from the manual: "maximum value of *expression* across all input values") > greatest(*value* [, ...]) is not an aggregate function > (from the manual: "The GREATEST function selects the largest value from a > list of any number of expressions.") > > So max takes the maximum of values coming from several rows, while greatest > takes the maximum of values coming from 1 row. > > Assuming > select model,count(distinct cars) > from rc_cars > group by model > returns more than one record you will need both functions. > Greatest gets the most recent date out of "bought" and "returned" PER > RECORD. > Max gets the most recent date out of all these greatest dates OVER > ALL RECORDS. > > > Model Bought Returned X 2004-08-25 2005-01-01 -> Greatest = 2005-01-01 > X 2006-02-17 2006-02-18 -> Greatest = 2006-02-18 X 2005-11-13 2001-05-16 > -> Greatest = 2005-11-13 ¯ Max = 2006-02-18 > That being said, isn't it unlikely that "bought" is more recent than > "returned"? > I can imagine that one can only return a car after buying it. > If so, writing > ... WHERE max(returned) < current_date - interval '1 day' ... > would be enough. > Another thought: with this WHERE clause a car returned yesterday will not > show up. > Is that what you want? If not, use > ... WHERE max(returned) < current_date ... > > Good luck > > > >>> "Ruben Gouveia" [EMAIL PROTECTED]> 2008-09-11 19:33 >><[EMAIL PROTECTED]> > What is the difference between these two. I know that max() is an aggregate > function > > select model,count(distinct cars) > from rc_cars > where greatest(baught,returned) < current_date - integer '1' > group by model; > > Do i need to have a max () around a greatest() to make sure i get the most > recent of the two. > > select model,count(distinct cars) > from rc_cars > where max(greatest(baught,returned)) < current_date - integer '1' > group by model; > > >
[SQL] Doubts about FK
Hi there, In my DB I have a couple of FK, so the change of referenced columns is a quite complicated. Today, I DISABLE TRIGGER ALL on the tables envolved and then ENABLE TRIGGER ALL to back them. Is there a better way to do that? I read something about SET CONSTRAINTS ALL DEFFERED, but I don't know how to use it. Regards, Rafael Domiciano
Re: [SQL] Pls Hlp: SQL Problem
Dear Richard, Sorry for that :) and thanks for your tips, but it didn't solve my problem. Maybe because my postgresql knowledge limitation. I have tried your suggestion, my view like this : -- View: "vwlapjual" -- DROP VIEW vwlapjual; CREATE OR REPLACE VIEW vwlapjual AS SELECT tblpenjualan.tanggal, tblpenjualan."operator", tblpenjualan.noinvoice, tblpenjualan.bayar, tblpenjualan.bayarvocher, tblpenjualan.jam, tblpenjualan.id, tblpenjualan.jeniscard, tbltransaksi.kodeproduk, tbltransaksi.keluar, tbltransaksi.harga, tbltransaksi.disc1, tblproduk.namabarang, tblproduk.subkat, tblsubkategori.subkategori, tblkategoriproduk.kode, tblkategoriproduk.kategori, tbltransaksi.hargapokok * tbltransaksi.keluar::numeric(2,0) AS modal, tbltransaksi.keluar * tbltransaksi.harga - (tbltransaksi.disc1 / 100::numeric(3,0) * (tbltransaksi.keluar * tbltransaksi.harga))::numeric(10,0) AS jumlah, tbltransaksi.keluar * tbltransaksi.harga - tbltransaksi.disc1 / 100::numeric(3,0) * (tbltransaksi.keluar * tbltransaksi.harga) - (tbltransaksi.hargapokok * tbltransaksi.keluar)::numeric(10,0) AS profit FROM tblpenjualan JOIN tbltransaksi ON tblpenjualan.id = tbltransaksi.jualid LEFT JOIN tblproduk ON tbltransaksi.kodeproduk::text = tblproduk.kode::text LEFT JOIN tblsubkategori ON tblproduk.subkat::text = tblsubkategori.id::text LEFT JOIN tblkategoriproduk ON tblsubkategori.kategoriid::text = tblkategoriproduk.kode::text; ALTER TABLE vwlapjual OWNER TO hpl; and the view result like this : "2008-07-20";"ULY";"01-200708";"Tunai";0.00;"1899-12-30 21:14:47+06:55:25";1;"";"8993417200021";1.00;4200.00;0.00;"ELIPS HAIR VITAMIN ";"121";"VITAMIN RAMBUT";"NF";"Non Food";3960.00;4200.;240. Thanks for any suggestion. Regards, Hengky Richard Huxton wrote: Hengky Lie wrote: Dear friends, Hello. Quick tip - don't reply to another message when starting a new thread, people will assume it's part of the old thread. [snip most of query] / 100::numeric * Food";3960.;*4200.*;*240.* Why the calculation result has so many decimals ? Because you've cast 100 to numeric, rather than numeric(10,2) or whatever. This means you get the full accuracy on the calculation - it won't ever trim a numeric (that's the whole reason to have the type).
Re: [SQL] Pls Hlp: SQL Problem
On Fri, Sep 12, 2008 at 12:50 PM, Hengky Lie <[EMAIL PROTECTED]> wrote: > Dear Richard, > > Sorry for that :) and thanks for your tips, but it didn't solve my problem. > Maybe because my postgresql knowledge limitation. I have tried your > suggestion, my view like this : Put parens around the whole thing, like: (tbltransaksi.hargapokok * tbltransaksi.keluar)::numeric(2,0) AS modal Otherwise you're really doing this: tbltransaksi.hargapokok::numeric * tbltransaksi.keluar::numeric(2,0) AS modal and when you multiply a numeric times a numeric(2,0) you get a numeric, not a numeric(2,0) -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Doubts about FK
On Fri, Sep 12, 2008 at 12:14 PM, Rafael Domiciano <[EMAIL PROTECTED]> wrote: > Hi there, > > In my DB I have a couple of FK, so the change of referenced columns is a > quite complicated. > Today, I DISABLE TRIGGER ALL on the tables envolved and then ENABLE TRIGGER > ALL to back them. > Is there a better way to do that? Depends. Are other people connected to the server when you do it? disable trigger disables the triggers for everybody, not just you if I remember correctly. If other folks are using the db, then they can insert bad data during that period. > I read something about SET CONSTRAINTS ALL DEFFERED, but I don't know how to > use it. the constraints have to created as deferrable to do that. then, in a transaction, you can do something like: begin; update in a way that would normally violate an FK insert in a way that fixes the previous statement's FK relationship commit; and it will work as long as the constraints all make sense by the time you get to commit. Note that unique constraints are not deferrable in pgsql. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Pls Hlp: SQL Problem
> -Mensaje original- > De: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] En nombre de Scott Marlowe > > On Fri, Sep 12, 2008 at 12:50 PM, Hengky Lie > <[EMAIL PROTECTED]> wrote: > > Dear Richard, > > > > Put parens around the whole thing, like: > > (tbltransaksi.hargapokok * tbltransaksi.keluar)::numeric(2,0) AS modal > Consider that if you are NOT going to use the decimals you should really use integer or bigint datatypes. The numeric type compute much slower than integer datatypes. Regards, Fernando -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Pls Hlp: SQL Problem
On Fri, Sep 12, 2008 at 4:01 PM, Fernando Hevia <[EMAIL PROTECTED]> wrote: > > >> -Mensaje original- >> De: [EMAIL PROTECTED] >> [mailto:[EMAIL PROTECTED] En nombre de Scott Marlowe >> >> On Fri, Sep 12, 2008 at 12:50 PM, Hengky Lie >> <[EMAIL PROTECTED]> wrote: >> > Dear Richard, >> > >> >> Put parens around the whole thing, like: >> >> (tbltransaksi.hargapokok * tbltransaksi.keluar)::numeric(2,0) AS modal >> > > Consider that if you are NOT going to use the decimals you should really use > integer or bigint datatypes. The numeric type compute much slower than > integer datatypes. Note that if you're just converting the output the cost is minimal compared to if you're doing all your math in numeric. It's when you force math to happen that numeric is slower, but numeric's gotten a lot of tuning in the last few years and it's withing a few percentage of integer for most measurements. Definitely not twice as slow or anything like they once were. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
